Forum Discussion

Hussein_charif's avatar
Hussein_charif
Icon for Helper V rankHelper V
1 year ago
Solved

Running total average in matrix not working properly

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?
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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.

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        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

         

  • suparnababu8's avatar
    suparnababu8
    Icon for Super User rankSuper User
    1 year ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

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