Forum Discussion

arielfilipa's avatar
arielfilipa
Advocate I
9 years ago
Solved

Year-To-Date Calculation

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 ?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Reports
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    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

3 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    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's avatar
      arielfilipa
      Advocate I
      9 years ago

      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