Forum Discussion
Community ChampionSmart DAX Last Year To Date
Question for those DAX Gurus amoung us.
I have the measures in place for YTD Sales and Last YTD Sales. These return values that are spot on but I want more. What I would like to create is a measuer for Last YTD that cuts off the calculations based upon yesterday's date so that I can plug it in to my reports and not have to worry about updating filters to cut off the data for a particular user who wants up-to-date sales figures. As of now, it pulls in last year YTD sales through the end of my filter. So, if I am filtering by the month it pulls in through the end of the month where I only want last years sales through the current date.
Is it possible to build this to pull the last invoiced date found in the sales information and back that up a year so that I don't have to add granular filters?
My Sales table is tied to my date table by both Order Date and by Posting Date. Posting Date uses USERELATIONSHIP.
For YTD Invoiced Sales I am using: =TOTALYTD([Total Sales by Posting Date], DimDate[DateKey])
For Last YTD Sales I am using : =CALCULATE([Invoiced Sales YTD], DATEADD(DimDate[DateKey], -1, year))
6 Replies
- Greg_Deckler
Can you do something like:
=CALCULATE([Invoiced Sales YTD], DATEADD(DimDate[DateKey], -1, year) && DimDate[DateKey] < TODAY()) Or perhaps =CALCULATE([Invoiced Sales YTD], DATEADD(DimDate[DateKey], -1, year), DimDate[DateKey] < TODAY())
Greg_Deckler I tried to use your formula it works well when you have CARD visuals, but when I have table chart with states having 5 years data. it gives me all data and I am supposed to get only last year current month data.
How to achieve this.
Thanks,
Thimma
- Greg_Deckler
Can you post some sample data so that I can play with the solution?
