Thanks for your tips on working with YTD LY formulas, they were very helpful for me. Currently I'm struggling with the following issue. I have ar report with a single Year Slicer, data is loaded with one month back period - there is a DATEADD formula which substracts 1 month from YTD LY to match the data.
I have a YTD and YTD LY measures, so for month 04.2019 they will show following data:
- 2019 YTD - sum of months 1,2,3 2019
- YTD LY - sum of months 1,2,3 2018
But when I change year slicer to show the last year data, which should be the full year data, DATEADD formula substracts -1 from months, so:
- 2018 YTD I have sum of all months 1 - 12 2018
- YTD LY - it shows data 11 months, 1 - 11 2017
I've tried to apply some IF formula to solve that using Calendar table, but I can't use calendar table for logical test.
Here is my YTD LY formula for this report:
SomeIndicator YTD LY = VAR DataMaxDate = CALCULATE ( MAX ( kalendarz[Date].[Date] ); ALL ( kalendarz[Date] ) ) RETURN CALCULATE ( [ SomeIndicator YTD]; SAMEPERIODLASTYEAR ( DATEADD( INTERSECT ( VALUES ( kalendarz[Date].[Date] ); DATESBETWEEN ( kalendarz[Date].[Date]; BLANK (); TODAY() ) ); -1;MONTH) ) )
Just clarifying your requirements...
Is this the behaviour you want?
- When you filter on a year that is entirely in the past (i.e. 2018 or earlier as at today), you want to filter on the entire year for both the YTD and YTD LY measures?
- When you filter on the current year (i.e. 2019 as at today), you want to adjust the YTD and YTD LY filters so include only months up to the previous month based on today's date (i.e. up to March if today is in April).
And, maybe it is important too, I have data in fact table by moth-year. So it is connected to Calendar Table by month-year key.
Hi again @mat_k
I tinkered around with this one a bit.
Below are some measures that I believe should do what you want.
One note - I recommend you mark your kalendarz table as a Date Table, so you don't have to use the automatically generated date hierarchies with the dot-notation. The below measures assume you've done this.
The logic is essentially to limit the YTD date filter to only those dates up to and including the end of last month, using the INTERSECT function.
The IF(...) also ensures that the measures don't return a result for dates after the end of last month.
SomeIndicator YTD = VAR EndOfLastMonth = EOMONTH ( TODAY (); -1 ) RETURN IF ( MAX ( kalendarz[Date] ) <= EndOfLastMonth; VAR DateFilterYTD = INTERSECT ( DATESYTD ( kalendarz[Date] ); DATESBETWEEN ( kalendarz[Date]; BLANK (); EndOfLastMonth ) ) RETURN CALCULATE ( [SomeIndicator]; DateFilterYTD ) )
SomeIndicator YTD LY = VAR EndOfLastMonth = EOMONTH ( TODAY (); -1 ) RETURN IF ( MAX ( kalendarz[Date] ) <= EndOfLastMonth; VAR DateFilterYTD = INTERSECT ( DATESYTD ( kalendarz[Date] ); DATESBETWEEN ( kalendarz[Date]; BLANK (); EndOfLastMonth ) ) VAR DateFilterYTD_LY = SAMEPERIODLASTYEAR ( DateFilterYTD ) RETURN CALCULATE ( [SomeIndicator]; DateFilterYTD_LY ) )
Let me know if this works for you.
Hello @OwenAuger ,
Many thanks for your effort so far.
LY YTD measure works fine but only if I use months. For entire years, measeure is calculated only for past years, for current year is blank. And I need it to display for full year.
I've created pbix file with some random sample data for this case. Date is still as [Date].[Date] because my PowerBi app crashes after marking table as date table:)
@mat_k Thanks for testing this out 🙂
A small tweak is required to my original suggestion - thanks for spotting!
The part of both measures that tests whether the Max Date is <= EndOfLastMonth should actually test whether Min Date is <= EndOfLastMonth. i.e. it should be changed to
IF ( MIN ( 'Calendar'[Date].[Date]) <= EndOfLastMonth
With this change, you will see the expected result for years that are partially complete. The logic is that you want to return a result as long as the filtered date range begins on or before EndOfLastMonth.
I used the update for YTD to
For those of you who have gone through all the formulas on the 5 pages so far on this blog and still haven't found a working solution for you....
I FINALLY found one that works for my data set from here: https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/
Here's the formula that ended up working for me:
CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DATESBETWEEN(Calendar[date], STARTOFYEAR(Calendar[Date] ), LASTNONBLANK(Calendar[Date], [Total Sales]))))
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.