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 extracts into a sharepoint online folder every hour.

From here, I take a combination of all text files into my Power Query.

 

With each extract I have a column which I've created as the 'Extraction Date' which is a date/time field and has the date and hour of extraction.

 

What I'm looking to do is to calculate the variance between (based on the extraction date/time):

 

1. The most current report against the previous most current report (the variance in the last hour)

2. The 9am report from today against the 9am report from yesterday

 

There are multiple figures I'd be calculating the variance for, so what I'm looking for is for Power Bi to dynamically recognise the required data (based on the 2 variables above) and give me the variances accordingly.

 

Is this possible?

 

Need Help

1 Reply

  • 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.