Running total average in matrix not working properly

[Hussein_charif]

i have a measure to get "Avg cost" using it in my matrix :

AVG Cost=

VAR MinDate =

    CALCULATE(

        MIN(ValTB[Date]),

        ALL(ValTB)

    )

VAR MaxDate =

    MAX(ValTB[Date])

RETURN

    CALCULATE(

    DIVIDE(

        SUM(ValTB[Cost]),

        SUM(ValTB[Quant]),

        0)
),

        FILTER(

            ALL(ValTB[Date]),

            ValTB[Date] >= MinDate &&

            ValTB[Date] <= MaxDate

        )

    )


in my matrix i have the rows:
Item, year, month, day. the measure should not take the start date in my date slicer to consideration to calculate the avg cost, only the end date, which works perfectly fine, but within my matrix, for example if i am checking the avg cost for the month of september in 2024 for an item, the avg cost for september is not correct, because it calculates the measure with the start date only being from the 1st day of 2024, not the first day in my table, since i am filtering under 2024.
for more clearance:

for the above item in september, it should be 113, but it is showing 137 because it is considering the start date as jan 1st 2024, without taking to consideration the previous years.

is there a way to fix this?

[v-nuoc-msft · Accepted solution]

Hi @Hussein_charif 

Try this:

AVG Cost = 
VAR MinDate = MIN('ValTB'[Date])
VAR MaxDate = MAX('ValTB'[Date])

RETURN
CALCULATE(
    DIVIDE(
    SUM(ValTB[Cost]),
    SUM(ValTB[Quant]),
    0),
    FILTER(
        ALL(ValTB),
        ValTB[Date] >= MinDate &&
        ValTB[Date] <= MaxDate
    )
)

 When you choose to check data for September, it ignores previous dates and only considers data for September.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Is your FY from April to March?

[danextian]

Hi @Hussein_charif 

A separate date dimensions table with a complete set of dates make time intelligence calculations easier.

You can see in the screenshot below that even when the year changes, the calculations from the previous periods are still being carried over to the current.

Sample calculations are in the attached pbix.

[Hussein_charif]

unfortunatly, this did not work either

[Hussein_charif]

hi @danextian,

i'll try this and let you know.
thanks

[danextian]

The problem with your measure is that the average calculation is applied to the whole date column only

 FILTER(
            ALL(ValTB[Date]),
            ValTB[Date] >= MinDate &&
            ValTB[Date] <= MaxDate
        )

 Now, you can't be applying that to the whole ValTB table  or you will get the same value for the whole table with respect to the columns in your variables like in the screenshot below

[Hussein_charif]

I removed that context when re applying the measure, but it gave me a complete different set of values which are all incorrect

[danextian]

How did you remove the context? Where is it applied to? Can you please share a sanitzed copy  of your pbix?

[suparnababu8]

Hi @Hussein_charif 

I think  your measure  does not consider the start date in your date slicer but only the end date.  Therfore, you could modify the measure to ignore the date slicer for the start date. try the below measure. 

AVG Cost =
VAR MinDate =
    CALCULATE(
        MIN(ValTB[Date]),
        ALL(ValTB))
VAR MaxDate =
    MAX(ValTB[Date])
RETURN
    CALCULATE(
        DIVIDE(
            SUM(ValTB[Cost]),
            SUM(ValTB[Quant]),0),
        FILTER(
            ALL(ValTB[Date]),
            ValTB[Date] >= MinDate &&
            ValTB[Date] <= MaxDate
        ),
        REMOVEFILTERS(ValTB[Date]))

Let me know if it works. Thanks

[Hussein_charif]

hi @suparnababu8 , this returned the same exact values as my prev measure

[suparnababu8]

Let me check and get back.

[Hussein_charif]

much appreciated🙏