Forum Discussion
TY and LY Running Total Formulas
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 |
|
|
|
1 Reply
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" ) )
