Previous Quarter Sales Measure is NOT Working

[Anonymous]

 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.

[v-shex-msft]

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

[Anonymous]

Hi @v-shex-msft ,

Thanks for the response and i tried with ALL as well but not working.

[v-shex-msft]

HI @Anonymous ,

Can you please explain more detail about your scenario? If my formula not help, please share a pbix file for test.

Regards,

Xiaoxin Sheng

[Anonymous]

Sure, i am trying to upload PBIX but not finding the option to upload?

Coud you please anyone share me the steps to upload an PBIX file.

[v-shex-msft]

Hi @Anonymous ,

You can upload to onedrive or google drive, then share link here.
Notice: do mask on sensitive data before share.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Team,

Any Update Please.......