Forum Discussion

MalikMahamoor's avatar
MalikMahamoor
Regular Visitor
4 years ago

How do I create dynamic variance calculation based on continuously updating date criteria?

I've got a dataset which has the same data (identical format, just updated figures), which is extracted every hour.   These tables are extracted from my ERP system via text file which schedules the...
Need Help
MFelix's avatar
MFelix
Super User
4 years ago

Hi MalikMahamoor ,

 

Without further information is difficult to give the correct answer but you need to do something around this.

 

I created two measures:

PreviousReport =
CALCULATE (
    SUM ( 'Table'[Value] ),
    TOPN (
        1,
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < MAX ( 'Table'[Date] ) ),
        'Table'[Date], DESC
    )
)

PreviousDay = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    TOPN (
        1,
        FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] = MAX ( 'Table'[Date] ) - 1  ),
        'Table'[Date], DESC
    )
)

 

In this case I'm just picking up the values but you can then make variations:

To make it easier to calculate you can use calculation groups and a slicer to select wich value you need.