Forum Discussion

angelo2022's avatar
angelo2022
Frequent Visitor
4 years ago
Solved

Problem with Running Totals for Prior Year

Hi,  I'm struggling with the DAX to calculate the running total for the Prior Year.   The Running total for the current Financial year works fine though.     To get this working I have Two date ta...
DAX
Prior Year
running total
STLY
  • amitchandak's avatar
    amitchandak
    4 years ago

    angelo2022 , Try like example

     

    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30"))

     

    or

     

    new column

    Day of Year =datediff([Year Start date] , [Date],Day) +1

     

    measures

    YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
    LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

     

    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

angelo2022 , Try like example

 

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30"))

 

or

 

new column

Day of Year =datediff([Year Start date] , [Date],Day) +1

 

measures

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1 && 'Date'[Day of Year] <= Max('Date'[Day of Year])))

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

  • angelo2022's avatar
    angelo2022
    Frequent Visitor
    4 years ago

    Thanks amitchandak .  You are the man.... That worked nicely...👍

  • angelo2022's avatar
    angelo2022
    Frequent Visitor
    4 years ago

    Hi amitchandak ,
    Thanks for helping with this one, but could please help me again.   


    We have moved to the next financial Year (Jul 2022 to June 2023) but it doesn't seem to calculate a Running Totals for Current and Prior Months for Previous Financial Years unless I add the Year into the visual.  

    I had to adjust the Measure Running Total a little
    From this: 

    //Current Year Running Total 

    [Measure Running Total] = CALCULATE([Measure1], DATESYTD(DimDateYTD[YTDDateValue], "30/06"))

     

    To this: 

    [Measure Running Total] =
    VAR IsCurrentFYear = SELECTEDVALUE(DimDate[IsCurrentFY])
    RETURN
    IF(IsCurrentFYear = TRUE(),
    CALCULATE([Measure1], FILTER(DimDateYTD, DimDateYTD[YTDDateValue] <= TODAY())),
    CALCULATE([Measure1], DATESYTD(DimDateYTD[YTDDateValue],"30/06")))
    //It uses a different calculation if filtering to the current Financial Year. 

    I used this for the Running total for STLY
    [Measure STLY RunningTotal] =CALCULATE([Measure1 STLY], DATESYTD(DATEADD(DimDateYTD[YTDDateValue], -1,YEAR),"30/6"))

    Hope it makes sense and you can help.  
    Thanks Angelo