Forum Discussion
Previous Quarter Sales Measure is NOT Working
Hi Team,
Good Morning and hope doing good.
I have a issue with Previous Quarter Calculation and details are below.
Please note that i dont have any date column in my model and sample data looks like below table.
Sales Margin = [Latest Quarter Sales] -[Prev Quarter Sales]
Latest Quarter Sales = CALCULATE(
SUM(Sales_Table[Sales Amt])
,FILTER(ALLSELECTED(Sales_Table),Sales_Table[Fiscal Quarter Rank]=MAX(Sales_Table[Fiscal Quarter Rank]))
)
Prev Quarter Sales = CALCULATE(
SUM(Sales_Table[Sales Amt])
,FILTER(ALLSELECTED(Sales_Table),Sales_Table[Fiscal Quarter Rank]=MAX(Sales_Table[Fiscal Quarter Rank])-1)
)
As you can see from below image, the Previous Quarter just works fine.
The Expected Output is:-
When i select any Quarter from Slicer then The Previous Quarter Sales( Q3-18 value -- > 300 ) should display in the screen.
Is there any workaround using DAX
And i am trying to attach complete PBIX file but i dont know how to upload here ?please help?
Thanks in advance.
6 Replies
Hi Anonymous ,
I think you need to use all function to replace allselected function.
When you apply slicer/filter on your table, allselected function will enable these filter effect to your formula, power bi can't find out correspond record in filtered table records.Prev Quarter Sales = VAR currRank = MAX ( Sales_Table[Fiscal Quarter Rank] ) RETURN CALCULATE ( SUM ( Sales_Table[Sales Amt] ), FILTER ( ALL ( Sales_Table ), Sales_Table[Fiscal Quarter Rank] = currRank - 1 ) )Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT
Regards,
Xiaoxin Sheng