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

Answer 1

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 [e-mail].

Answer 2

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

Answer 3

I haven't tested this, but would this measure work:

CALCULATE(
 TABLE[MEASURE_NAME],
 DATESBETWEEN(
  TIME[DATE],
  LASTDATE(TIME[DATE]) - 30,
  LASTDATE(TIME[DATE])
 )
)