topic Re: Different results dependent on slicer in DAX Commands and Tips

Different results dependent on slicer

LCCKevO — Tue, 07 Jun 2022 15:25:00 GMT

I have a built financial reports that includes measures for "variance" and "change in variance from last period". The report has slicers for "Financial Year" and "Period". Most of the time these work how I want them to.

However, when "Period" is filtered to "1" the "change in variance from last period" calculates as the difference between the last value for the previous year, and the first of the selected year. It should just be the "variance" figure, as at the start of the year there is no variance. I thought the way around this was to use an if function, but when I've tried it doesn't work.

The DAX I've tried so far, is as below:

= If ([Period] = "1", "Variance", "Change in Variance")

Any suggestions on how I can get this to work?

Re: Different results dependent on slicer

amitchandak — Wed, 08 Jun 2022 03:11:34 GMT

LCCKevO , the way to deal with any nonstandard period

Say on YYYYMM

Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

Period Rank = RANKX(all(Period),Period[Period Start Date],,ASC,Dense)

measure
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Re: Different results dependent on slicer

Anonymous — Fri, 10 Jun 2022 08:40:54 GMT

Hi LCCKevO ,

In order to give you a suitable solution, could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

In addition, please review the following links and check if they can give you some insights or direction.

Different Measures Based on Slicer Selection in Power BI

DAX – Calculating the difference between 2 selections on a Slicer

Difference between results based on slicer selection

Variance from Selected Companies = VAR summary = ADDCOLUMNS (ALLSELECTED(Item_Table[Company]), "cCompany", RANKX(ALLSELECTED(Item_Table[Company]),[Grand Total])) VAR S1= CALCULATE([Grand Total],FILTER(summary,[cCompany]=1)) VAR S2= CALCULATE([Grand Total],FILTER(summary,[cCompany]=2)) Return S1-S2

Best Regards

Re: Different results dependent on slicer

LCCKevO — Mon, 13 Jun 2022 08:51:15 GMT

Thanks Anonymous

The video on Different Measures based on Slicer Selection in Power BI provided an answer to my problem. This is going to be very useful. 🙂