Displaying metrics with prior years based of selected year slicer value

[Anonymous]

I need a quick fix to a seemingly simple issue here :

Measures are Rejection rate and Invoice count.

I have a Year Slicer.

1. When I select the year I want to display a measaure:  Rejection rate for selected year and two preceding years (2021, 2020, 2019) 

2. I also want to be able to display another measure:  Invoice count for all years before 2021.

Below is my simple solution (using a table) for  years before 2021.  

*Temp Sum count for years =

CALCULATE ([*Total invoice count],

FILTER (All ('DimDate'),

'DimDate'[calYear] > SELECTEDVALUE('DimDate'[calYear])))

I expect ony years less than selected years to be pulled, but something seems off.  I made sure to turn interactivity of slicer with table.

*Selected year = SELECTEDVALUE(DimDate[CalDate].[Year])

Result:

[parry2k · Accepted solution]

@Anonymous I think @danextian  already have a measure for this but need a bit of tweaking before the selected year need < (less than sign) not less than equal to (<=) otherwise that will include selected year value as well:

Total Before Selected Year = 
CALCULATE (
    SUM ( 'Fact'[Value] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year] < SELECTEDVALUE ( 'Disconnected Calendar'[Year] )
    )
)

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Anonymous I agree and endorse the @danextian solution. I haven't looked at his file but his reply is on the right track.

[danextian]

Hi @Anonymous ,

My approach would be to use a disconnected Dates table (one that has not relationship to any of of the Fact or Dates tables). Please see sample PBIX.  https://drive.google.com/file/d/1ktSLpKFEjGIzLb3RzcIqxY75AbheS_iS/view?usp=sharing 

[Anonymous]

Awesome!  Yes, this works. 

I attempted disconnecting the date table by disabling interactivity between the Slicer and the table/graph.   

What dax would you apply for graph of measure count for ALL years before selected year (2021) ?  

[danextian]

Hi @Anonymous ,

This was my formula in the attached file

Total Before Selected Year = 
CALCULATE (
    SUM ( 'Fact'[Value] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year] <= SELECTEDVALUE ( 'Disconnected Calendar'[Year] )
    )
)

@parry2kis right. It should have been ( just < instead of <=)

Total Before Selected Year = 
CALCULATE (
    SUM ( 'Fact'[Value] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Year] < SELECTEDVALUE ( 'Disconnected Calendar'[Year] )
    )
)

[Ashish_Mathur]

Hi,

See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.

[Anonymous]

No, it does not help.   I need a DAX formular to solve both cases instead.