Answered by:
Get the 30 previous days in measure DAX (SSAS Tabular)

Question
-
Hello,
I try to create a measure in DAX that retrun me : The value of previous 30 Days of one measure. For example measure that return the bill of 30 days.
The problem is : I got special dimension Time, for example :
Day Week Month 24/01/2018 1 1 25/01/2018 1 1 26/01/2018 2 1 27/01/2018 2 1 28/01/2018 2 1 29/01/2018 2 1 30/01/2018 2 1 31/01/2018 3 1 01/02/2018 3 2 02/02/2018 3 2 03/02/2018 3 2 04/02/2018 3 2 05/02/2018 4 2 06/02/2018 4 2 07/02/2018 4 2 Like you can see the number of week follow specific rule and the problem is that i manage to create some measures but that work only with days field. When i use hierarchy (the hierarchy got this member : day, week, year) That don't make me the agregate. In fact, i don't have any values for the week for example but still got value for the day. So please, anyone have an idea?
Regards,
Amar
Reponse
Tuesday, February 26, 2019 3:57 PM
Answers
-
I haven't tested this, but would this measure work:
CALCULATE(
TABLE[MEASURE_NAME],
DATESBETWEEN(
TIME[DATE],
LASTDATE(TIME[DATE]) - 30,
LASTDATE(TIME[DATE])
)
)Wednesday, February 27, 2019 10:39 PM
All replies
-
Hi Adghar,
I don't know how you design your model, did you create relationship between date dimension and fact table? If possible, could you please inform me more detailed information(such as your data sample, your relationship between corresponding dimension, you corresponding measure and you expect output)? Then I will help you more correctly.
Best Regards,
Zoe Zhi
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.Wednesday, February 27, 2019 2:07 AM -
Hello, thanks for the reply and there are my tabular model :
Dimension Time :
Date_key (bigint) Day (date) Week (int) Month (int) Name of Week (NVARCHAR(12)) Name of Month (Nvarchar(50)) 24012018 24/01/2018 1 1 2018W02 January 25012018 25/01/2018 1 1 2018W02 January 26012018 26/01/2018 2 1 2018W03 January 27012018 27/01/2018 2 1 2018W03 January 28012018 28/01/2018 2 1 2018W03 January 29012018 29/01/2018 2 1 2018W03 January 30012018 30/01/2018 2 1 2018W03 January 31012018 31/01/2018 3 1 2018W03 January 1022018 01/02/2018 3 2 2018W04 January 2022018 02/02/2018 3 2 2018W04 January 3022018 03/02/2018 3 2 2018W04 January 4022018 04/02/2018 3 2 2018W04 February 5022018 05/02/2018 4 2 2018W05 February 6022018 06/02/2018 4 2 2018W05 February 7022018 07/02/2018 4 2 2018W05 February and got hierarchy called Week like :
-Year
- Name of Week
- Day
Fact table :
Date_key (bigint) Measure 1 24012018 2 25012018 56 26012018 63 27012018 85 28012018 102 29012018 3 30012018 520 31012018 36 1022018 50 2022018 41 3022018 33 4022018 95 5022018 84 6022018 19 7022018 11 The fact table and time dimension are related with date_key column. And i wanna create measure 2 that calculate the the 30 previous day of measure and display it with the hierarchy Week.
Regards,
Amar
Reponse
Wednesday, February 27, 2019 7:03 AM -
I haven't tested this, but would this measure work:
CALCULATE(
TABLE[MEASURE_NAME],
DATESBETWEEN(
TIME[DATE],
LASTDATE(TIME[DATE]) - 30,
LASTDATE(TIME[DATE])
)
)Wednesday, February 27, 2019 10:39 PM