Different results dependent on slicer

[LCCKevO]

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?

[v-yiruan-msft · Accepted solution]

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

[LCCKevO]

Thanks @v-yiruan-msft 

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. 🙂

[amitchandak]

@LCCKevO , the way to deal with any nonstandard period

Say on YYYYMM

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

or

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