topic Re: Running total until today in DAX Commands and Tips

Running total until today

joshua1990 — Sun, 05 Feb 2023 20:27:43 GMT

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] ) ) )

Re: Running total until today

Greg_Deckler — Sun, 05 Feb 2023 20:37:41 GMT

joshua1990 Better Running Total - Microsoft Power BI Community

Re: Running total until today

joshua1990 — Mon, 06 Feb 2023 06:30:05 GMT

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.

Re: Running total until today

Greg_Deckler — Mon, 06 Feb 2023 12:57:31 GMT

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

Re: Running total until today

Anonymous — Tue, 07 Feb 2023 01:33:34 GMT

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