Calculate variances in cost based on the dates selected in the slicer

[JishnuRajiv]

I have a dataset which stores the cost for a day and the cost for the immedietly previous day and then their variance. This way I have a day by day variance. Now my stakeholders wants an option where they would select, lets say 4 dates from the date dropdown slicer and they need to see  the variance(dynamically calculated). For example if they select 2023-10-01, 2023-10-22 and 2023-11-21 from the date drop down they need to see the cost variance for 2023-10-01 as 0, variance for 10-22 as cost for (10-22) - (10-01) and similary for 2023-11-21, it should be cost for (11-21) - (10-22). I have been trying to solve this and couldn't get it to work for more than 2 dates.(It kind of works for 2 dates)

My measure looks like this; I have also attached a sample from my data.

Dynamic Variance =

VAR STARTDATE = MIN('DIM DATE'[Date])

VAR ENDDATE = MAX('DIM DATE'[Date])

VAR STARTCOST = CALCULATE(

                            SUM('Variance'[COST]), FILTER(ALL('Variance'),

                              'Variance'[Cost Date] = STARTDATE

                            ))

VAR ENDCOST = CALCULATE(

                        SUM('Variance'[COST]),FILTER(ALL('Variance'),

                                'Variance'[Cost Date] = ENDDATE

                            ))

RETURN

    IF(ISBLANK(STARTCOST) || ISBLANK(ENDCOST), BLANK(), ENDCOST - STARTCOST)

Application	Cost Date	Cost	Previous Day's Cost	Variance($)
APM	11/18/2023	$142	$94	$48
APM	11/19/2023	$30	$20	$10
APM	11/17/2023	$27	$19	$8
APM	11/19/2023	$150	$142	$8
APM	11/17/2023	$94	$86	$7
APM	11/20/2023	$29	$24	$5
APM	11/19/2023	$24	$20	$5
APM	11/24/2023	$9	$4	$5
APM	11/21/2023	$8	$3	$4
APM	11/21/2023	$8	$3	$4
APM	11/23/2023	$82	$77	$4
APM	11/20/2023	$4	$0	$4
APM	11/24/2023	$8	$4	$4
APM	11/17/2023	$29	$25	$4
APM	11/21/2023	$51	$48	$3
APM	11/21/2023	$51	$48	$3
APM	11/21/2023	$51	$48	$3
APM	11/24/2023	$23	$20	$3
APM	11/27/2023	$24	$21	$3
APM	11/21/2023	$7	$4	$2
APM	11/21/2023	$4	$2	$2
APM	11/21/2023	$27	$25	$2
APM	11/27/2023	$11	$9	$2
APM	11/21/2023	$5	$3	$2
APM	11/20/2023	$3	$1	$2
APM	11/16/2023	$22	$20	$2
APM	11/15/2023	$23	$22	$2
APM	11/20/2023	$11	$9	$2
APM	11/18/2023	$47	$45	$2
APM	11/18/2023	$47	$45	$2

BWO	11/27/2023	$1	$1	$0
BWO	11/14/2023	$19	$19	$0
BWO	11/25/2023	$13	$13	$0
BWO	11/20/2023	$0	$0	$0
BWO	11/27/2023	$0	$0	$0
BWO	11/15/2023	$6	$6	$0
BWO	11/27/2023	$0	$0	$0
BWO	11/22/2023	$7	$6	$0
BWO	11/23/2023	$1	$1	$0
BWO	11/16/2023	$1	$1	$0
BWO	11/21/2023	$1	$0	$0
BWO	11/20/2023	$1	$0	$0
BWO	11/25/2023	$7	$7	$0
BWO	11/16/2023	$4	$4	$0
BWO	11/20/2023	$4	$4	$0
BWO	11/23/2023	$4	$4	$0
BWO	11/14/2023	$4	$4	$0
BWO	11/25/2023	$4	$4	$0
BWO	11/27/2023	$4	$4	$0
BWO	11/14/2023	$4	$4	$0
BWO	11/18/2023	$4	$4	$0
BWO	11/14/2023	$4	$4	$0
BWO	11/16/2023	$4	$4	$0
BWO	11/19/2023	$7	$7	$0
BWO	11/26/2023	$7	$7	$0
BWO	11/20/2023	$6	$6	$0
BWO	11/20/2023	$1	$1	$0
BWO	11/18/2023	$10	$10	$0
BWO	11/24/2023	$10	$10	$0
BWO	11/17/2023	$8	$7	$0
BWO	11/21/2023	$10	$10	$0

BWR	11/14/2023	$2	$1	$0
BWR	11/14/2023	$2	$1	$0
BWR	11/16/2023	$1	$0	$0
BWR	11/23/2023	$1	$0	$0
BWR	11/15/2023	$4	$3	$0
BWR	11/27/2023	$1	$0	$0
BWR	11/16/2023	$6	$6	$0
BWR	11/27/2023	$1	$0	$0
BWR	11/14/2023	$15	$14	$0
BWR	11/21/2023	$13	$13	$0
BWR	11/14/2023	$2	$2	$0
BWR	11/27/2023	$1	$0	$0
BWR	11/20/2023	$1	$1	$0
BWR	11/14/2023	$2	$2	$0
BWR	11/14/2023	$2	$2	$0
BWR	11/22/2023	$0	$0	$0
BWR	11/24/2023	$6	$6	$0
BWR	11/20/2023	$1	$1	$0
BWR	11/23/2023	$22	$22	$0
BWR	11/23/2023	$22	$22	$0
BWR	11/24/2023	$22	$22	$0
BWR	11/27/2023	$22	$21	$0
BWR	11/19/2023	$14	$14	$0
BWR	11/15/2023	$2	$2	$0
BWR	11/15/2023	$2	$2	$0
BWR	11/15/2023	$2	$2	$0

expecting help from experts

[gmsamborn]

Hi @JishnuRajiv 

Would something like this help?

Change = 
AVERAGE( 'FactVariance'[Cost] ) -
    CALCULATE(
        AVERAGE( 'FactVariance'[Cost] ),
        OFFSET(
            -1,
            ALLSELECTED( 'DimDate'[Date] ),
            ORDERBY( 'DimDate'[Date] )
        )
    )

JishnuRajiv.pbix

Let me know if you have any questions.

[JishnuRajiv · Accepted solution]

Thank you so much @gmsamborn . I really appreciate the quick help.

I changed the formula from average to sum(because there were some more dimensions coming between application- I removed it to simplify the data so that I can paste here) and it is working as expected.

I now understand that I was missing the offset dax. Honestly I didnt even know it existed. Thank you so much.
If you dont mind, could you please help me understnad how the _Include measure works??

[gmsamborn]

@JishnuRajiv 

The [_Include] measure finds the first and last dates in the Variance table.  It checks to see if the current record is between the 2 dates.

It used in the Filters pane to filter the Date slicer so dates outside of the range don't appear in the slicer.

(It's irrelevant to this solution.)

[JishnuRajiv]

Okay, got it. I already used another measure to filter out the dates column in the date dimension table to only show dates having data in my fact table. But this solution seems more relevant as it shows all the dates falling between the start and end dates in the fact table.(my solution only shows dates having data). Thanks again.