Month over Month Variance fails to update when filter/slicer applied to table

[MesserAmericas]

I am comparing overtime per month at 100+ locations, the OT and OT last month update when slicer / filter by location is selected, however the variance and variance % do not reflect the change. It continues to return total value of OT for the entire month for all locations.

How can I fix my formula to update by location when filtered for accurate reporting?

[PaulDBrown]

Try the following:

Sum OT = SUM (Table[OT])

Prev Month OT = CALCUALTE([Sum OT], DATEADD(Date Table[Date], -1, DAY))

OT Month Var = [Sum OT] - [Prev Month OT]

[PaulDBrown]

how is the model set up? Do you have a Date Table?

[MesserAmericas]

Yes I have a date table. 

[MesserAmericas]

Were you able to understand the sample data. My formula does not contain any notes regarding Filter. I am new to power BI

[PaulDBrown]

please provide sample data or PBIX file, a depcition of the expected output and the code for the measure you have

[MesserAmericas]

Sample data OT by month, when slicing by location the variance pulls entire total.

Month	Location	OT
January	LocA	500
January	LocB	1300
January	LocC	1100
January	LocD	200
January	LocE	0
January	LocF	1700
February	LocA	0
February	LocB	2500
February	LocC	7000
February	LocD	2
February	LocE	1600
February	LocF	400
March	LocA	600
March	LocB	1000
March	LocC	0
March	LocD	500
March	LocE	250
March	LocF	500

Formula : OT MoM Var = OT Last Month - OT Sum

[Anonymous · Accepted solution]

Hi @MesserAmericas,

You can try to use the following formula to extract the current date value to calculate.

formula =
VAR currDate =
    MAX ( 'Date'[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR cmOT =
    SUM ( Table[OT] )
VAR pmOT =
    CALCULATE (
        SUM ( Table[OT] ),
        FILTER (
            ALLSELECTED ( Table ),
            YEAR ( [Date] ) = YEAR ( prevDate )
                && MONTH ( [Date] ) = MONTH ( prevDate )
        )
    )
RETURN
    cmOT - pmOT

Regards,

Xiaoxin Sheng