I got transacions for the year 2015 from Jan till Dec
and for the year 2016 from Jan till Jun
I want to create KPIs and the users don't want to select / filter out anything
what I mean is that I want to create a line chart showing a line for YTD amount for 2016 vs YTD amount for 2015
for 2016 I created the following expression:
YTD Revenues = var maxYear = max(Master_Calendar[max_Year]) return CALCULATE(sum(view_operations[Amount]),view_account_categorization[Fiscal_Category_DescE]="Budget",view_account_categorization[Fiscal_Chapter_DescE]="Revenues",FILTER(all(Master_Calendar),Master_Calendar[Year]=maxYear && Master_Calendar[Date] <= max(Master_Calendar[Date])))
for 2015 I created the following expression
LYTD Revenues = CALCULATE([YTD Revenues],PREVIOUSYEAR(Master_Calendar[Date]))
However I get the following results which is not logical
If you want to use PREVIOUSYEAR(Master_Calendar[Date]) in this scenario, you have to use a slicer to select current year so that PREVIOUSYEAR() will get correct current year to resolve.
Please try with below formula of “LYTD Revenues”.
LYTD Revenues = CALCULATE ( [YTD Revenues], PREVIOUSYEAR ( FILTER ( VALUES ( Master_Calendar[Date] ), YEAR ( Master_Calendar[Date] ) = MAX ( Master_Calendar[max_Year] ) ) ) )
I would recommend combining a Year-Month column and using two years worth of data instead of a single year. Filter for sales data for 2015 and 2015 in query editor. Add your year-month column to your date table to display as 2015-01, 2015-02 etc. Then use just YTD and use YearMonth on the axis.
Check out the News & Announcements to learn more.
Microsoft received the highest score of any vendor in both the strategy and current offering categories.
Mark your calendars and join us for our next Power BI Dev Camp!
Check out our new Discover Your Career Path blog post series and get all the details.