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?
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.
@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?
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.