Forum Discussion
Compare sameperiodlastyear, DAX does not work , and solution is cumbersome - looking for short cut
Issue: To compare Current period vs SamePeriodLastyear, I find I need to create a "New Measure".
Let me illustrate,
i want to find "diiference" between this year vs last year.. so I created a new measure = Last Financial Yr salesUsing SUM, I cannot see "Sales LFY"using SUMX, the math is wrong in REDended up Create a new measure "Sales (dax) only then the SUMX works
Is there an simpler way to get the right outcome without creating "Sales (dax) = SUM(Sheet1[sales]) "
6 Replies
- MattAllington
Community Champion
I can't really tell what you are doing here as you have only provided parts of the model. In short, assuming you have a calendar table that meets the rules, this should work
Total Sales = sum(table[salesColumn])
Total Sales LY = calculate([Total Sales],sameperiodlastyear(calendar[dates]))
chg vs LY = [Total Sales] - [Total Sales LY]
you could replace Total Sales with this if you wanted to... sumx(table,table[salesColumn]) but it is effectively the same thing in this instance.
rules on calendar tables
Thanks.. your suggestions is to create "total Sales " which the same as the sales column...Which is the same solution I ended up with...
Can the "change v LY" calculation work without having to create "total sales,".....If this is unavoidable, can you explain this logic - for my learning... Many thanks. Malcolm
- MattAllington
Anonymous wrote:Thanks.. your suggestions is to create "total Sales " which the same as the sales column...Which is the same solution I ended up with...
Well technically the measure Total Sales is not the same as the sales column. When you drag and drop a numeric column into the Values section, Power BI implicitly creates a measure for you in the background. All I have done is expicitly write this same measure. The advantages of doing this are many, including:
- If you want to learn DAX, this gives you practice
- You can give it the exact name you want and don't have to rely on what is given by Power BI
- You can then reuse this measure inside other measures (as I have
Anonymous wrote:Can the "change v LY" calculation work without having to create "total sales,".....If this is unavoidable, can you explain this logic - for my learning... Many thanks. Malcolm
Yes, you could write this
Total Sales LY = calculate(sum(table[salesColumn]),sameperiodlastyear(calendar[dates]))
But I don't see much benefit of doing this. I encourage you to write all your base measures and then reuse them inside other measures. It is just like Excel - it is possible to create a complex formula in a single cell, but it is much easier to break the problem into interim pieces and solve each part of the puzzle as a single problem before finally combining all the pieces into the end solution
Hello Matt,
below are the measures i have created to calcuated YoY Amount. but Amount Paid(PY) measure is coming blank though i have data.
can you help here, i'm not sure where i am doing wrong.
Amount Paid:=CALCULATE(SUM(Payouts[Payout Amount]),FILTER(Payouts,Payouts[Payout Order Status Code]="Payment:PaymentSent" || Payouts[Payout Order Status Code]="Payment Sent"))
Amount Paid(PY):=CALCULATE([Amount Paid],SAMEPERIODLASTYEAR('Calendar'[Calendar Date]))
YoY Amount Paid:=[Amount Paid]-[Amount Paid(PY)]
Thanks in Advance
- MattAllington
the formula looks fine. It could be any number of issues, you just have to work it through.
Layout a table with Year, Month on rows and the affected measures in the table. Check you get the expected results for Amount Paid and Amount Paid(PY) for all periods. If not, you have to investigate the possible cause.
