Average of a measure between two dates

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I am struggling with something that I thought would be pretty simple, but…

I have already a measure that gives me a percentage-calculation. This measure I use to compare months over months. I now want to compare it to a baseline over a given set of dates: 01012018 to 31072018. My current formula is this:

Baseline = Calculate([Wastage Percentage];
           DATESBETWEEN(DimCalendarDaily[CalendardailyKey];date(2018;01;01);date(2018;12;31)))

The result I get is the same as the original measure… it seems that it is not using the datesbetween function.
I use a Calendar table which is related to the fact table from where I derive the original measure.
Any hints to how I get this one working??

2

This will likely be to do with the context of the calculation.

Is the context in your report page between exactly the same dates?

If so then it will just be returning the same this as you’ve opened up a date range that already exists.

Are you looking to find an average results over the time frame?

Also want is the Wastage Percentage formula.

If you can add an example file I would probably be able to tell the issue very quickly and get the correct formula required for what you need.

Chrs

3

HI Sam

After working some more on this I think I can boil down the problem to this:
I need the measure to do the already mentioned calculation, since that will be my baseline. This measure I need to get to work in a row context: “batch numbers” also if they are included in the datesbetween filter:

Baseline = Calculate([Wastage Percentage];
DATESBETWEEN(DimCalendarDaily[CalendardailyKey];date(2018;01;01);date(2018;07;31)))

Since I want to compare the measure called “Wastage Percentage” with the new measure called Baseline and the row context is the batch numbers. I have tried numerous combination of ALL and ALLEXCEPT, but nothing gives me the baseline in the batch number context.
I would have thought that the following did the trick, but no:

Baseline ALLexcept Produced batch = 
Calculate(
[Wastage Percentage];
ALL(DimCalendarDaily[CalendarDailyKey]);ALL(Table XYZ);ALLEXCEPT(Table XYZ;Table XYZ[Produced Batch]);
DATESBETWEEN(DimCalendarDaily[CalendardailyKey];date(2018;01;01);date(2018;07;31))
             )

The below table is an example of what I want to accomplish:
image

The reason the baseline is not identical for all batches is that they are different products. See below table:
image
But I do not want to show the products in the visual.
Does it make any sense? Thanks!

4

I’m a bit confused on this sorry.

I’ll likely need to see and example to truly know what a solution can be.

Finding it difficult to imagine the many moving parts here around the data model, context etc.

Can you work up an example file and add this to the post?

Personally just after reviewing this multiple times my feel is it will be that difficult but I need to be more precise on the many factors at play.

The new formula you’re using seems way to complicated. I don’t believe anything like this will be required.