locked
Get the 30 previous days in measure DAX (SSAS Tabular) RRS feed

  • 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])
     )
    )

    • Proposed as answer by auaero Wednesday, February 27, 2019 10:39 PM
    • Marked as answer by Adghar Thursday, February 28, 2019 9:16 AM
    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])
     )
    )

    • Proposed as answer by auaero Wednesday, February 27, 2019 10:39 PM
    • Marked as answer by Adghar Thursday, February 28, 2019 9:16 AM
    Wednesday, February 27, 2019 10:39 PM