cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Measure different from Total

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.

1 ACCEPTED SOLUTION
Super User

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.

Proud to be a Super User!

2 REPLIES 2
Super User

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.

Proud to be a Super User!

Resolver I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors