Year-To-Date Calculation

[arielfilipa]

Hi,

I have a raw data with the information per Yearmonth divided between priorities and with a Created sum.

Then in the Power BI Desktop, I've uploaded this information and created a variable to calculate the YTD.

YTD = 
VAR RowDate = 'YTD'[Yearmonth]
RETURN
    CALCULATE (
        SUM ( 'YTD'[Created] );
        FILTER (
            ALL('YTD');
            'YTD'[Yearmonth] <= RowDate
			 && YEAR ( 'YTD'[Yearmonth]) = YEAR ( RowDate )    
        )
    )

And it works however with a slicer for the priority the YTD doesn't changed.

Can you please help me ?

[v-haibl-msft · Accepted solution]

@arielfilipa

It seems that you created a calculate column for the YTD. To make it changed based on the selection, you need to create a calculated measure with following formula.

YTD_Measure = 
VAR RowDate =
    CALCULATE ( MAX ( 'YTD'[Yearmonth] ) )
RETURN
    CALCULATE (
        SUM ( 'YTD'[Created] ),
        FILTER (
            ALLSELECTED ( 'YTD' ),
            'YTD'[Yearmonth] <= RowDate
                && YEAR ( 'YTD'[Yearmonth] ) = YEAR ( RowDate )
        )
    )

Best Regards,
Herbert

[arielfilipa]

Hi Herbert (@v-haibl-msft),

Now I would like to show just a shorter number of yearmonths but if I remove yearmonths the YTD values change and the YTD for P12 2016 for example isn't the total of the year even if I have the data available in the raw data.

Is there a way to shorten the view but keeping the values ?

Thanks,

Filipa

[arielfilipa]

Hi Herbert (@v-haibl-msft),

It works 🙂

Thanks!! 🙂