Rolling Revenue - hiding future dates

[swongbg]

Hi All

I have created a 5-day rolling revenue measure using calculate/datesinperiod and I would only want the rolling revenue to show up until today (in this example, today would be 11/5/2020).

I have tried to play around the filters but wasn't able to hide this unless I fix the date fields filter to show on or before 11/5/2020 - this will mean a manual update every day? Referring to the images below, I would like to show photo 2.

Thanks All!

[amitchandak]

@swongbg , Best if you can do, Put a visual level filter on this date, Say Last 2 year(Any duration witch work for you) including today

[AlB]

Hi @swongbg 

Add acondition to your measure so that it returns a blank if the date is later than today. If you need more details you'd have to show the code for your rolling-revenue measure and where/how is being used 

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[Mariusz · Accepted solution]

Hi @swongbg 

You can add if statement to limit the data to the max date in a given table.

Sales Running Total = 
VAR mc = MAX('Calendar'[Date])
VAR mf = CALCULATE(MAX(Sales[OrderDateKey]), ALL(Sales)) >= mc
RETURN 
IF(
    mf,
    CALCULATE(
        [Sales],
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= mc
        )
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[swongbg]

Many thanks, the dates without sales have disappear.

Instead of summing up everything from the first day, where I will put the formula to only sum the past 5 days? Inside the mf variable I am guessing?

[v-deddai1-msft]

Hi @swongbg ,

Time smart functions require a complete calendar table, we should avoid using them, the following measure has the same effect：

measure =
VAR a =
    MAX ( 'table'[Date] )
RETURN
    CALCULATE (
        SUM ( 'table'[Total Revenue] ),
        FILTER ( ALL ( 'table' ), 'table'[Date] <= a && 'table'[Date] > a - 5 )
    )

Best Regards,

Dedmon Dai