Forum Discussion

Jidnyasa2904's avatar
Jidnyasa2904
Icon for Helper I rankHelper I
1 year ago

Trailing 12 month YOY Comparison

I have this Dax code for the shifting period for the dashboard,
Example: To view data just for the day, for a week, for a month, for YTD, Full year. After that, I want to add Trailing 12 months. but somewhere code is not working. 

I do have separate year filters to dynamically decide which year will be This year vs Last year. 

Time Period=
Var TodayDate = CALCULATE(LASTDATE('Date Table'[Date]), FILTER('Sales Table', 'Sales Table'[Sales] > 0))
Var TodayDateDOY = CALCULATE(Max('Date Table'[Day of Year]), FILTER('Date Table', 'Date Table'[Date] = TodayDate))

 

Var YearStart = CALCULATE(FIRSTDATE('Date Table'[Date]), FILTER('Date Table', 'Date Table'[Year] = YEAR(TodayDate)))
Var MonthStart = CALCULATE(FIRSTDATE('Date Table'[Date]), FILTER('Date Table', 'Date Table'[Year] = YEAR(TodayDate)), FILTER('Date Table', 'Date Table'[Month] = MONTH(TodayDate)))
Var MonthStartDOY = CALCULATE(Min('Date Table'[Day of Year]), FILTER('Date Table', 'Date Table'[Date] = MonthStart))

 

Var WeekStart = CALCULATE(FIRSTDATE('Date Table'[Date]), FILTER('Date Table', 'Date Table'[Year] = YEAR(TodayDate)), FILTER('Date Table', 'Date Table'[Month] = MONTH(TodayDate)), FILTER('Date Table', 'Date Table'[Fiscal Week] = CALCULATE(Max('Date Table'[Fiscal Week]), FILTER('Date Table', 'Date Table'[Date] = TodayDate))))
Var WeekStartDOY = CALCULATE(Min('Date Table'[Day of Year]), FILTER('Date Table', 'Date Table'[Date] = WeekStart))

 

Var TodayDate23 = CALCULATE(LASTDATE('Date Table'[Date]), FILTER('Date Table', 'Date Table'[Day of Year] <= TodayDateDOY && 'Date Data'[Year] = 2023))
Var YearStart23 = CALCULATE(FIRSTDATE('Date Table'[Date]), FILTER('Date Table', 'Date Table'[Day of Year] = 1 && 'Date Table'[Year] = 2023))
Var MonthStart23 = CALCULATE(FIRSTDATE('Date Table'[Date]), FILTER('Date Table', 'Date Table'[Day of Year] = MonthStartDOY && 'Date Table'[Year] = 2023))
Var WeekStart23 = CALCULATE(FIRSTDATE('Date Table'[Date]), FILTER('Date Table', 'Date Table'[Day of Year] = WeekStartDOY && 'Date Table'[Year] = 2023))

 

Var Trailing12MonthsStart = CALCULATE(FIRSTDATE('Date Table'[Date]), DATESINPERIOD('Date Table'[Date], TodayDate, -12, MONTH))
Var Trailing12MonthsStart23 = CALCULATE(FIRSTDATE('Date Table'[Date]), DATESINPERIOD('Date Table'[Date], TodayDate23, -12, MONTH))

 

Var TM =
UNION(
    ADDCOLUMNS(
        CALENDAR(YearStart, TodayDate),
        "Selection", "Year to Date", "Order", 5
    ),
    ADDCOLUMNS(
        CALENDAR(YearStart, YearStart + 363),
        "Selection", "Full Year", "Order", 6
    ),      
    ADDCOLUMNS(
        CALENDAR(MonthStart, TodayDate),
        "Selection", "Month to Date", "Order", 4
    ),
    ADDCOLUMNS(
        CALENDAR(WeekStart, TodayDate),
        "Selection", "Week to Date", "Order", 3
    ),
    ADDCOLUMNS(
        CALENDAR(TodayDate, TodayDate),
        "Selection", "Previous Day", "Order", 1
    ),
    ADDCOLUMNS(
        CALENDAR(Trailing12MonthsStart, TodayDate),
        "Selection", "Trailing 12 Months", "Order", 7
    ),
    ADDCOLUMNS(
        CALENDAR(YearStart23, TodayDate23),
        "Selection", "Year to Date", "Order", 5
    ),
    ADDCOLUMNS(
        CALENDAR(YearStart23, YearStart23 + 370),
        "Selection", "Full Year", "Order", 6
    ),
    ADDCOLUMNS(
        CALENDAR(MonthStart23, TodayDate23),
        "Selection", "Month to Date", "Order", 4
    ),
    ADDCOLUMNS(
        CALENDAR(WeekStart23, TodayDate23),
        "Selection", "Week to Date", "Order", 3
    ),
    ADDCOLUMNS(
        CALENDAR(TodayDate23, TodayDate23),
        "Selection", "Previous Day", "Order", 1
    ),
    ADDCOLUMNS(
        CALENDAR(Trailing12MonthsStart23, TodayDate23),
        "Selection", "Trailing 12 Months", "Order", 7
    )
)

 

Return
TM

Any help is appreciated!
Anonymous Ashish_Mathur Anonymous 

Need Help

7 Replies

    • Jidnyasa2904's avatar
      Jidnyasa2904
      Icon for Helper I rankHelper I
      1 year ago

      Hi Ritaf1983 Anonymous,

      Yes. The above Dax Code is to generate a Time Interval Selector for selecting different time ranges in Power BI.

      Assuming I have 5 Grocery stores, filtered to Year-to-Date. 

      StoresThis YearLast Year
      1 $        20,000 $        18,000
      2 $        15,020 $        16,036
      3 $        19,000 $        18,000
      4 $        17,560 $        21,001
      5 $        17,000 $        15,550
      • I have a Year Selection filter, so the This Year column is a measure that looks at the max year similar to last year.
      • I have the Dax code for Time Interval Selection ( Previous Day, Week to Date, Month to Date, Year to Date, Full Year, Trailing 12 months).
      • I think the Trailing 12-month logic is not working in the above code because I have the Year Selection filter. For example, This year is 2024, so the Trailing 12 month cannot grab data from 2023. Similar to last year as well. 





       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi All,
    Firstly  Ritaf1983 thank you for your solution!
    And Jidnyasa2904 ,I think the meaning of your DAX code is to generate a Time Interval Selector for selecting different time ranges in Power BI. The function of this time interval selector is to help the user quickly switch between different time perspectives to view the data, right?

    In our attempt, we applied a similar approach to help you achieve this requirement, first we create a new table to provide filtering to the user, and then we filter based on the data in the table on the basis of our Date Table Make sure that this value of yours is dynamic Hopefully, this will help you with your question!

     

    TimePeriodChoices = 
DATATABLE(
    "TimePeriod", STRING,
    {
        {"Year to Date"},
        {"Month to Date"},
        {"Week to Date"},
        {"Trailing 12 Months"}
    }
)
    Dynamic Total Sales = 
VAR SelectedPeriod = SELECTEDVALUE(TimePeriodChoices[TimePeriod], "Year to Date")  
VAR TodayDate = TODAY()
VAR YearStart = DATE(YEAR(TodayDate), 1, 1)
VAR MonthStart = DATE(YEAR(TodayDate), MONTH(TodayDate), 1)
VAR WeekStart = TodayDate - WEEKDAY(TodayDate, 2) + 1
VAR Trailing12MonthsStart = EDATE(TodayDate, -12)

RETURN
SWITCH(
    SelectedPeriod,
    "Year to Date", CALCULATE(SUM('Sales Table'[Sales]), 'Date Table'[Date] >= YearStart && 'Date Table'[Date] <= TodayDate),
    "Month to Date", CALCULATE(SUM('Sales Table'[Sales]), 'Date Table'[Date] >= MonthStart && 'Date Table'[Date] <= TodayDate),
    "Week to Date", CALCULATE(SUM('Sales Table'[Sales]), 'Date Table'[Date] >= WeekStart && 'Date Table'[Date] <= TodayDate),
    "Trailing 12 Months", CALCULATE(SUM('Sales Table'[Sales]), 'Date Table'[Date] >= Trailing12MonthsStart && 'Date Table'[Date] <= TodayDate),
    BLANK()  
)

     

    If you have more recent questions, you can contact us at any time, we will be the first time to reply to you after receiving your message, looking forward to your reply!

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.