Forum Discussion

zfemmer's avatar
zfemmer
Helper I
5 years ago
Solved

Running Total Measures

I have a few measures that the end user can choose from that will feed into the visuals; WTD, MTD, YTD, last 30 days, etc.

 

The end user has now asked to have the option to see the revenue numbers by day or period to date running total.

 

I have written this:

Test = 
VAR _MaxDate = MAX('Date'[Date])
VAR _MeasureCategory = SELECTEDVALUE(_TimeCompareMeasureCategory[Measure])

VAR _PeriodToDate =
CALCULATE(
    SUM(RevenueChannel[BookedRevenue]),
    'Date'[Date] <= _MaxDate,
        FILTER(ALL('Date'),
        'Date'[Date] < TODAY() &&
        'Date'[Date] >= TODAY() - 7
        )
)
VAR _DayByDay =
CALCULATE (
    SUMX (
        FILTER(
            GROUPBY (
                RevenueChannel,
                RevenueChannel[SearchType],
                RevenueChannel[ChannelType],
                RevenueChannel[ChannelName],
                'Date'[Date],
                'Date'[AccountingWeek],
                'Date'[WeekNumber],
                'Date'[AccountingMonthEnglishAbbrYear],
                'Date'[AccountingMonth],
                'Date'[MonthNumber],
                'Date'[AccountingYear],
                "Revenue", SUMX ( CURRENTGROUP (), RevenueChannel[BookedRevenue] )
            ),
        'Date'[Date] >= TODAY()-7
        && 'Date'[Date] < TODAY()
        ),
    [Revenue]
    )
)
RETURN
SWITCH(
    TRUE(),
    _MeasureCategory = "Day By Day", _DayByDay,
    _MeasureCategory = "Period To Date", _PeriodToDate
)

 

This works except for whatever reason when the end user is selecting period to date it shows up like this:

How do I get this to only show up to today? It seems to do the calculations up to today, but then just has a static number after that. I need to be able to do this within the measure, not with a slicer.

  • Just wrap the result inside an if statement 

     

    if([some measure that doesn't return a result into the future]<>0,your result)

1 Reply

  • MattAllington's avatar
    MattAllington
    Community Champion

    Just wrap the result inside an if statement 

     

    if([some measure that doesn't return a result into the future]<>0,your result)