Cumulative Run Rate for a category with a slicer

[AbbyLear]

Hey guys, 

Ran into a problem when trying to slice up my visuals and it'd be great if anyone can help out.

My table looks like this (calling this "Table"): 

I want to create a line chart, with dates on the X-axis, cumulative paid/case as Y-axis, and the payer as legend. In addition, I need to apply a slicer for Case date and payer so that the user can toggle the visual between different date ranges, and by payer (and allowes for multiple selection for payer to see trend comparisions). For example, if user selected case dates between 1/1 and 5/30 and payer "Cigna", the chart would show a monthly trend for Cigna that goes from $10/case in march, to $30/case in april, and $55/case in May ($110 culumative payment / 2 cumulative cases). 

I have performed the following: 

1. Created a measure:

    Total Payment = Sum ('Table'[Payment])

2. Crated a measure:

    Case Volume = Distinctcount('Table'[Case#])

3. Created a measure:

    Cumulative Payment = CALCULATE ( [Payment], FILTER ( ALLEXCEPT('Table', 'Table'[Payer]), 'Table' [Payment Date] < = Max ('Table' [Payment Date])))

4. Created a measure:

    Cumulative Volume - similar to the DAX formula under #3 above. 

5. Lastly, I created a measure which is simply Cumulative Payment / Cumulative Volume as the "run rate" 

The problem that I ran into is that the cumulative DAX is ignoring the filter from the slicer, so the result is the same no matter what "case date" I select. I was getting around this by using "ALLSELECTED", but given I have to calculate cumulative data by payer, I used "ALLEXCEPT" instead. Is there anyway to add an "allselected" somehow into the calculate formula so that the measures created will follow the slicer formula? Or maybe I'm going about this the wrong way?

Thanks for your help in advance! 

[v-zhangti · Accepted solution]

Hi, @AbbyLear 

You can try the following methods.

Maesure:

Cumulative Payment = 
CALCULATE (
    [Total Payment],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Payment Date] <= MAX ( 'Table'[Payment Date] )
            && [Payer] = SELECTEDVALUE ( 'Table'[Payer] )
    )
)

Cumulative Volume = 
CALCULATE (
    [Case Volume],
    FILTER (
        ALL ( 'Table' ),
        'Table'[Payment Date] <= MAX ( 'Table'[Payment Date] )
            && [Payer] = SELECTEDVALUE ( 'Table'[Payer] )
    )
)

run rate = [Cumulative Payment]/[Cumulative Volume]

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@AbbyLear , in Such a case it is always better to join your date with a date table and use date table on Axis

Assume payment is  a measure

A new measure

CALCULATE ( [Payment], FILTER ( all('Date') , 'Date' [Date] < = Max ('Date' [Date])))

or

CALCULATE ( [Payment], FILTER ( allselected('Date') , 'Date' [Date] < = Max ('Date' [Date])))

Use date from date from date table on axis

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[AbbyLear]

@amitchandak  Thanks for the help! I did not mention in my post earlier but I do have a dynamic canlendar look up table setup. I have created columns in the calendar lookup table for start of month ( as I want to show runrate changes by month) and have linked the two tables with dates to "payment date" in the original table. I am not sure how the DAX you provided would solve my problem as I need the run rate by payer. It also needs to let user select multiple payers on the slicer for comparison. If you use "allselected" and select multiple payers, that didn't work (the values are not correct.)