Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

YTD filter <= Today

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!

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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)

     

     

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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