Forum Discussion

joshua1990's avatar
joshua1990
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Running total until today

Hi all!

I have a simple line bar chart that displays the latest information (Sum Value) vs the Budget.

The line represents the budget accumulated per day for the selected month.

The bar represents the actual sales.

Now I would like to display a bar chart for the current month with a line for the full month but bars just for the past and today.

This measure displays the running total sum until today also for the next days.

How can I cut this by today?

Runnint Total = 
CALCULATE(
    SUMX(
        'Calendar',
        [Sales]
    ),
    FILTER(
        ALL( 'Calendar' ),
        'Calendar'[Year]
            = VALUES( 'Calendar'[Year] )
            && 'Calendar'[Month Num]
                = MAX( 'Calendar'[Month Num] )
            && 'Calendar'[Date]
                <= MAX( 'Calendar'[Date] )
    )
)
Need Help

4 Replies

    • joshua1990's avatar
      joshua1990
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      Greg_Deckler : Thanks a lot for sharing this alternative. But please note, my question is different > How can I cut a running total chart for the whole month (distributed per day) but with just values for the past?

      Using your approach I still get a values for tomorrow etc.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        3 years ago

        joshua1990 Handling tomorrow values is pretty easy, you just need an IF statement. 

          // Your code that results in a running total in a VAR named __RT
  VAR __Result = IF(MAX('Date'[Date]) > TODAY(),BLANK(),__RT)
RETURN
  __Result

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  joshua1990 ,

    I created some data:

    Here are the steps you can follow：

    1. Create measure.

    past and today =
IF(
MAX('Calendar'[Date])>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
&&
    MAX('Calendar'[Date])<=TODAY(),
SUMX(
    FILTER(ALL('Calendar'),
    'Calendar'[Date]<=MAX('Calendar'[Date])&&'Calendar'[Year]=MAX('Calendar'[Year])&&'Calendar'[Month Num]=MAX('Calendar'[Month Num])),[Sales]),BLANK())
    full month =
IF(
MAX('Calendar'[Date])>=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
&&
    MAX('Calendar'[Date])<=TODAY(),
SUMX(
    FILTER(ALL('Calendar'),
    'Calendar'[Year]=MAX('Calendar'[Year])&&'Calendar'[Month Num]=MAX('Calendar'[Month Num])),[Sales]),BLANK())

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly