cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Keropi79 Frequent Visitor
Frequent Visitor

Last Year YTD kept showing the whole year's amount

Hi,

 

I've been looking into the YTD topic calculations but none of the solution works for my situation.

My formula is as follow:

YTDRevenue = TOTALYTD(sum('P&L'[Revenue]),'Calendar'[Date],all('P&L'[GL_DATE]),"3/31")

LastYearYTDRevenue = CALCULATE(SUM('P&L'[Revenue]), SAMEPERIODLASTYEAR('Calendar'[Date]))

I also tried a slightly different formula for the LastYearRevenue:

LastYearYTDRevenue = CALCULATE([YTDRevenue], SAMEPERIODLASTYEAR('Calendar'[Date]))

However, both formula for LastYearRevenue yield me the whole revenue amount for last year, not the YTD last year revenue amount.

I've also tried DATESYTD and DATESADD syntax as well, but it's not working as well. 

 

The FY cut off is from Apr to Mar, not Jan to Dec.

Calendar table is as follow:

Calendar DateMonthQuarterFY
4/1/2019Apr-19Q1FY19
4/2/2019Apr-19Q1FY19
4/3/2019Apr-19Q1FY19
4/4/2019Apr-19Q1FY19
4/5/2019Apr-19Q1FY19
4/6/2019Apr-19Q1FY19
4/7/2019Apr-19Q1FY19
4/8/2019Apr-19Q1FY19
4/9/2019Apr-19Q1FY19
4/10/2019Apr-19Q1FY19
4/11/2019Apr-19Q1FY19
4/12/2019Apr-19Q1FY19

 

The P&L table is as follow:

GL_DATERevenue
3/4/2018       1,600
6/5/2018       4,000
9/9/2018       2,000
6/1/2019       5,000
3/3/2019       3,500
4/26/2019          100
5/27/2019       1,000
7/13/2019      30,000
9/13/2019       2,000
10/13/2019      30,000

 

The expected result I should get is: YTD Revenue = 63,100 ; Last Year YTD Revenue = 7,600. However, I keep getting Last Year YTD Revenue = 16,100.

 

Appreciate your help on this. Thanks.

2 REPLIES 2
Washivale Member
Member

Re: Last Year YTD kept showing the whole year's amount

Hi @Keropi79 ,

try changing your measures as below 

 

YTDRevenue = TOTALYTD(sum('P&L'[Revenue]),'Calendar'[Date],all('Calendar'[Date]),"3/31")

YTDRevenueLY = Calculate([YTDRevenue], SAMEPERIODLASTYEAR('Calendar'[Date]))

 

Use 'Calendar'[Date] column in your visuals as dimension and check if you are getting results as you need

 

Thank you,

Washivale

 

Keropi79 Frequent Visitor
Frequent Visitor

Re: Last Year YTD kept showing the whole year's amount

I tried this before, it's not working. If I change YTD to use the 'Calendar'[Date], my visualization will not generate anything, it'll just give the error message 'Something's wrong with one or more fields'.

Same case if I try to use the 'P&L'[GL_Date] for YTDRevenueLY syntax.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)