YTD filter <= Today

[Anonymous]

Hi Community

I have been struggling with some Cumulative calculation and the YTD Quick measure lately. I've gone through a lot of videos and tutorials but nothing has helped so far. All the methods I tried didn't give me the result.

I have managed to get both a budget YTD and actual YTD and I want to compare those. However, I need to stop the calculation for everything that is after today, because it essentially populates the last value for the rest of the entire year.

My code looks as follows:

Actual Tons YTD = 
	CALCULATE(TOTALYTD([Actual Tons]; 'PowerBI_Cargo Dim_DateDay'[Date].[Date]);
        FILTER(ALLSELECTED('PowerBI_Cargo Dim_DateDay'[Date]);'PowerBI_Cargo Dim_DateDay'[Date]<= TODAY()))

Budget Tons YTD = 
	CALCULATE(TOTALYTD([Budget Tons]; 'PowerBI_Cargo Dim_DateDay'[Date].[Date]);
        FILTER(ALLSELECTED('PowerBI_Cargo Dim_DateDay'[Date]);'PowerBI_Cargo Dim_DateDay'[Date]<=TODAY()))

It looks like the filter function I added does not work, it still gives me a horizontal line for the rest of the year for both measures.

I also tried MAX(Date) but as my date table goes way into the future (until the year 2100) this does not work either. Also, the actuals fact table does not have a date column but I link it with the "IdDay".

Anyone have clue?

Thanks a lot for your help!

[Phil_Seamark · Accepted solution]

Hi @Anonymous

Something like this tweak might work.  Note for my image I use the [Date] field from 'PowerBI_Cargo Dim_DateDay' on my Axis

Actual Tons YTD = 
VAR RetVal =
TOTALYTD(
        SUM([Actual Tons]), 
        'PowerBI_Cargo Dim_DateDay'[Date]
        )
        
RETURN 
    IF(
        MAX('PowerBI_Cargo Dim_DateDay'[Date])<=TODAY(), 
        RetVal)   

[Anonymous]

Hi @Phil_Seamark

Thanks a lot! That already solved half the puzzle I now get the cumulative to stop after today. But for some reason, it does not calculate the cumulative actuals for 2017. Only the budget

I have two years worth of data, both actuals, and budget...

[Phil_Seamark]

Oh that is because I only gave you a measure for [Actual tons].  Just copy the measure and replace with [Budget Tons] and add that measure to your visual

[Anonymous]

No, I did that already. Just investigated, looks like I had a data leak for 2017 data, so the calculation is correct!

Now it seems that I have a mixup in the date tables.
The function works just fine if I do not select the date hierarchy. But when I select it, it messes everything up.

My date table is a daily table of which I marked the column "Date" data type Date/Time.

See with date hierarchy:

And without above.

Also, the calculation is not running for subtotals:

Is it unable to do that because there is no date hierarchy in the table?

Sorry for this flood of questions, I guess once this is sorted I got clarity again 🙂

Thanks for the help!

Best regards

[Anonymous]

Hi @Phil_Seamark
Maybe you know the answer to the above question?

Your help is much appreciated!

Thanks in advance