Forum Discussion

greenskmachine2's avatar
greenskmachine2
Frequent Visitor
2 years ago
Solved

Incorrect Total for measure

Hi team, I am aware this is a common type of question but I haven't worked how to resolve it. 


I have a situation where I need to find the latest date(s), and then sum the data based between that date and ten days prior. 


I need this to display at all times, ignoring the date slicers. Below is what I have come up with, and it does give me the correct value per Plant. But how do I adjust it so the Total is actually the sum of what's above it? 

 

SumStationRows =
 
VAR MaxDate = CALCULATE(MAXX(F_Station,F_Station[CalendarDate]),ALL(DIM_Calendar))
VAR MaxDateStart = maxDate-10

VAR CalculateSum =
CALCULATE(
    CALCULATE(SUM(F_Station[RowCount]),
        FILTER(DIM_Calendar,DIM_Calendar[CalendarDate] >= MaxDateStart && DIM_Calendar[CalendarDate] <= MaxDate)),ALL(DIM_Calendar))

RETURN
CalculateSum

 

 

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure how your semantic model looks like, but please try something like below whether it suits your requirement.

     

    SumStationRows =
VAR MaxDate =
    CALCULATE ( MAXX ( F_Station, F_Station[CalendarDate] ), ALL ( DIM_Calendar ) )
VAR MaxDateStart = maxDate - 10
VAR CalculateSum =
    SUMX (
        VALUES ( 'PlantIDtable'[PlantId] ),
        CALCULATE (
            SUM ( F_Station[RowCount] ),
            FILTER (
                ALL ( DIM_Calendar ),
                DIM_Calendar[CalendarDate] >= MaxDateStart
                    && DIM_Calendar[CalendarDate] <= MaxDate
            )
        )
    )
RETURN
    CalculateSum