Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling Revenue - hiding future dates

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!

 

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    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

     

5 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

     

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-deddai1-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        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

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    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 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , 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