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 ...
  • 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
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
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