cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
balakrishna141 Regular Visitor
Regular Visitor

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)
)
Issue1.PNG
As you can see from below image, the Previous Quarter just works fine.
Issue2.PNG
 
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?
Issue3.PNG
 
Thanks in advance.
 

 

6 REPLIES 6
balakrishna141 Regular Visitor
Regular Visitor

Re: Previous Quarter Sales Measure is NOT Working

Hi Team,

 

Any Update Please.......

Community Support Team
Community Support Team

Re: Previous Quarter Sales Measure is NOT Working

Hi @balakrishna141 ,

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
balakrishna141 Regular Visitor
Regular Visitor

Re: Previous Quarter Sales Measure is NOT Working

Hi @v-shex-msft ,

 

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

Community Support Team
Community Support Team

Re: Previous Quarter Sales Measure is NOT Working

HI @balakrishna141 ,

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
balakrishna141 Regular Visitor
Regular Visitor

Re: Previous Quarter Sales Measure is NOT Working

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.

Community Support Team
Community Support Team

Re: Previous Quarter Sales Measure is NOT Working

Hi @balakrishna141 ,

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 192 members 1,885 guests
Please welcome our newest community members: