Measure different from Total

[azakir]

Hi Guys. 

Trying to work out this measure but can't seem to make it work. 

So I have the following measure: 

Measure = CALCULATE(SUM(Benchmarks[Benchmark Value]), 'Benchmark Measure'[Benchmark Measure Code] = "BCM", 'Benchmark Type'[Benchmark Type Description] = "Schedule 1MS" ,  

            DATESINPERIOD(

            'Calendar'[Calendar Date],

            LASTDATE('Calendar'[Calendar Date]),

            -7,

            DAY))

This calculates rolling 7 days from the date selected from filter. 

From the image below, it's not adding the values for 05/04 and 06/04 even though we have values for those days. Strangely enough, the total works absolutely fine. Can anyone guide me what or where am I wrong. And how to resolve this. Ideally I would like the total values 42541 and 48722 for those dates. 

[Wilson_ · Accepted solution]

azakir,

Personally I would re-write the measure to not use the time intelligence functions, like this:

Measure =
VAR MaxDate = MAX ( 'Calendar'[Calendar Date] )
VAR MinDate = MaxDate - 7
VAR Result =
CALCULATE (
    SUM ( Benchmarks[Benchmark Value] ),
    'Benchmark Measure'[Benchmark Measure Code] = "BCM", 
    'Benchmark Type'[Benchmark Type Description] = "Schedule 1MS",  
    REMOVEFILTERS ( 'Calendar' ),
    'Calendar'[Calendar Date] > MinDate,
    'Calendar'[Calendar Date] <= MaxDate
)

RETURN Result

I find it's a lot more explicit written this way and easier to debug, for me personally.

If that doesn't work for you, can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[azakir]

Hi @Wilson_ > Thank you so much mate. This formula resolved my issue. Still scratching my head why time intelligence didn't work. But no complains, I'll use this measure of yours. You're a life saver cheers