cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alihijazi
Helper I
Helper I

ytd and lytd without selecting a year

Hello

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 

Untitled.png

Please advise

6 REPLIES 6
v-sihou-msft
Microsoft
Microsoft

@alihijazi

 

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.

But you mentioned that you don’t want the users to select / filter out anything. So you can try with the formulas provided by @kcantor and @Vvelarde. Both formulas should work here.

 

Regards,

kcantor
Community Champion
Community Champion

@alihijazi

You can also use "DATEADD" for your calculation.

CALCULATE([YTDREVENUES], DATEADD(Master_Calendar[Date],-1, year))





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Vvelarde
Community Champion
Community Champion

hi; @alihijazi

 

try with this:

 

LYTD Revenues = CALCULATE([YTD Revenues],Sameperiodlastyear(Master_Calendar[Date]))




Lima - Peru

This function is not suitable since in 2016 we have data till June so for 2015 will only see data till June
Whereas we want to see for 2015 the whole year

@alihijazi

 

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] )
        )
    )
)

 

kcantor
Community Champion
Community Champion

@alihijazi

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors