Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unable to get LYTD info

Hello,

 

One of the Projects I am working on, I have to show YTD vs LYTD comparison on a bar chart. I calculated the YTD info using below formula:

 

JPM Account Segment YTD = TOTALYTD([JPM Account Segment], DateTable[Date],"09/30")

Please note that JPM Account Segment is a computed measure which gives me value as 396.18K

And then the JPM Account Segment YTD amount shows up as : 295.08K.

 

Now based on the YTD I calculated the LYTD using below DAX Formula.

JPM Account Segment LYTD = CALCULATE([JPM Account Segment LYTD], SAMEPERIODLASTYEAR(DateTable[Date]))

This gives me a value as 86.62K which should be 468.93K.

 

So the actual problem is the value I get using the LYTD is not the LYTD info but instead it sums up the value for entire fiscal year. The fiscal year runs from October 1st to September 30th which is why I used 9/30 as ending period date in the YTD formula. Can anyone help me correct the LYTD formula or correct me if I am doing anything wrong?

2 REPLIES 2
GilbertQ
Super User
Super User

Hi @Anonymous

 

Hi there just to double check your second measure has got the same name as your measure name, I am suspecting this is just a typo

 

What I would suggest doing is to put all your measures in a table like below, which you can then see if it is working as expected.

 

You measures do look to be correct.

 

image.png





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

@GilbertQ yes  that was a typo. I am going to reput the formuals for you with corect names.

Please see below formulas that I am using:

 

JP Morgan Chase Main Endowment Account = CALCULATE(ABS([Net Amount]), AccountTransactions[Account Number]="1-0000-1441") //Net Amount is a measure which is a difference between Debit Amount - Credit Amount. I am using Net Amount to compute the above measure.

 

JP Morgan Chase Main Endowment Account YTD = TOTALYTD([JP Morgan Chase Main Endowment Account], DateTable[Date],"09/30")

 

JP Morgan Chase Main Endowment Account LYTD = CALCULATE([JP Morgan Chase Main Endowment Account YTD], SAMEPERIODLASTYEAR(DateTable[Date]))

 

As you mentioned I put the Computed measures JP Morgan Chase Main Endowment Account YTD and JP Morgan Chase Main Endowment Account LYTD in a table and values look alright. But the problem is when I put the LYTD measure in a card then it gives me sum of the whole year which is 86,617.76 but I want to see LYTD value 468,931.28 which is the LYTD value as of April 20th, How do I do that?

 Scr1.JPGScr4.JPGscr3.JPG

 

 The goal is to put these measures on a clustered bar chart and see a side by side comparison of YTD vs LYTD. YTD value will be anyhow right but I want to make sure that LYTD bar on the chart does not sum the value for the whole year. Let' say today is 20th April so YTD is going to show me the value from October 1st 2017 to April 20th, 2018(which is till date). Similarly for LYTD I want to see value from October 1st 2016 to April 20th 2017 and then if we the date changes to April 21st then I would see LYTD info pertaining till April 21st and so on. Thanks.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors