Formula is summing instead of giving variance

[trupac]

Hi, 

I am trying to get a week to week variance and I tried using this measure but it looks like it's just adding all the volumes for a total of 1478.92 BOE instead of giving me the difference week to week:

Variance = CALCULATE(SUM('TDAILY_WELL_TNX (2)'[TOTAL NET BOE]), DATESBETWEEN('TDAILY_WELL_TNX (2)'[PRODUCTION_DATE],TODAY()-15,TODAY()-8)) - CALCULATE(SUM('TDAILY_WELL_TNX (2)'[TOTAL NET BOE]), DATESBETWEEN('TDAILY_WELL_TNX (2)'[PRODUCTION_DATE],TODAY()-7,TODAY()))

[jgeddes · Accepted solution]

At quick glance it appears that there is no way for your measure to calculate for the context supplied by the 'Production_Date' in your visuals.
You will likely have to replace the instances of 'Today()' with a reference to the 'Production_Date' context.

[Anonymous · Accepted solution]

Hi @trupac ,

Agree with jgeddes. Try adding a calendar table:

Calendar = CALENDAR(MIN('TDAILY_WELL_TNX (2)'[PRODUCTION_DATE]), MAX('TDAILY_WELL_TNX (2)'[PRODUCTION_DATE]))

And please try:

Variance = 
VAR __cur_date = SELECTEDVALUE('Calendar'[Date])
VAR __result =
CALCULATE (
    SUM ( 'TDAILY_WELL_TNX (2)'[TOTAL NET BOE] ),
    DATESBETWEEN (
        'Calendar'[Date],
        __cur_date - 15,
        __cur_date - 8
    )
)
    - CALCULATE (
        SUM ( 'TDAILY_WELL_TNX (2)'[TOTAL NET BOE] ),
        DATESBETWEEN ( 'Calendar'[Date], __cur_date - 7, __cur_date )
    )
RETURN
__result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[trupac]

Hi gao, thank you for the descriptive response. 

This might be a dumb question but I realize the code you gave me is not just a basic formula, looks more like SQL, where do I type the code in to get the results? I broke down the code by separating it out as formulas in my Calendar table but it didn't return the correct results.

[Anonymous]

Hi @trupac ,

This is a measure and you can create it here:

Best Regards,
Gao
Community Support Team

[jgeddes · Accepted solution]

At quick glance it appears that there is no way for your measure to calculate for the context supplied by the 'Production_Date' in your visuals.
You will likely have to replace the instances of 'Today()' with a reference to the 'Production_Date' context.