TY and LY Running Total Formulas

[mrpowrbihelp]

Hi,

We have a date table called ‘DateTbl’ and a ‘Sales file’ that are linked by the date column.

There is also a column in the date table called [Year] that states “Current Year” for dates in FY23 and “Previous Year” for dates in FY22. Our Fiscal year is from April – March. The date table goes out into the future.

We have the below formulas:

Sales Dollars = Calculate(Sum(‘Sales File’[Net Sales Dollars]))

TY YTD Cumulative $ = CALCULATE([Sales Dollars],FILTER(ALL(DateTbl),DateTbl[Date]<=MAX('Sales File'[Date])&&DateTbl[Year]="Current Year"))

Looking to create a visual like the below that only shows the Month Name not the year. The Month Name column is from the ‘DateTbl’. There are other filters on the page for things like Customer/Store/Month Name.

The above formula for TY YTD Cumulative $ seems to work okay, but when I try to make a LY YTD cumulative $ formula nothing is working. Any ideas on how to write these running total formulas for this year and last year to date?

It seems like a lot of formulas I’m seeing online do not like the year not being in the visual.

Month Name	LY Sales $	LY YTD Cumulative $	TY Sales $	TY YTD Cumulative $
April	560	560	659	659
May	876	1436	6696	7355
June	365	1801	445	7800
July	896	2697	254	8054
August	21445	24142	214	8268
September	2558	26700	6885	15153
October	5548	32248	2558	17711
November	6695	38943	6658	24369
December	146	38999	244	24613
January	885
February	669
March	2465

[gmsamborn]

Hi @mrpowrbihelp 

Would these measures help?

Sales = SUM( 'Sales'[Sales] )

YTD = 
    CALCULATE(
        [Sales],
        DATESYTD( 'Date'[Date], "03/31" )
    )

PY = 
    CALCULATE(
        [Sales],
        DATEADD(
            'Date'[Date],
            -1,
            YEAR
        )
    )

PY YTD = 
    CALCULATE(
        [PY],
        DATESYTD( 'Date'[Date], "03/31" )
    )

PY YTD 03-31 2.pbix