YTD Total from a measure

[yaman123]

Hi 

I have the below measure which shows the YTD volume for those who have left in the current month. I have a slicer with sample date column to choose between two dates. E.g if i select 01/02/2021 - 28/02/2021, i have 3 customers who left and the below measure shows the YTD volume for those 3 who left

YTD Retirer Volume =

CALCULATE(SUM(FM_MONTHLY_QTY_PBI[COLLECTION_LITRES]),DATESINPERIOD('FM_MONTHLY_QTY_PBI'[SAMPLE_DATE],MAX('FM_MONTHLY_QTY_PBI'[SAMPLE_DATE]),-12,MONTH),FM_MONTHLY_QTY_PBI[TERMINATION_REASON] = {"RETI"})

I need a new measure created which shows the YTD Total of the above measure. 

TIA 

[v-kkf-msft]

Hi @yaman123 ,

This is my sample data:

If you want to calculate the YTD volume for those 3 who left in the current month, try the following formula:

CurrentMonth_YTD = 
var ID_List = 
    CALCULATETABLE(
        DISTINCT(FM_MONTHLY_QTY_PBI[CustomerID]),
        FM_MONTHLY_QTY_PBI[TERMINATION_REASON] = "RETI"
    )
return 
    CALCULATE(
        SUM(FM_MONTHLY_QTY_PBI[COLLECTION_LITRES]),
        ID_List,
        DATESINPERIOD(
            'FM_MONTHLY_QTY_PBI'[SAMPLE_DATE],
            MAX('FM_MONTHLY_QTY_PBI'[SAMPLE_DATE]),
            -12,
            MONTH
        )
    )

If you want to calculate the YTD volume for all customers who had left, try the following formula:

AllDate_YTD = 
var ID_List = 
    CALCULATETABLE(
        DISTINCT(FM_MONTHLY_QTY_PBI[CustomerID]),
        FILTER(
            ALL(FM_MONTHLY_QTY_PBI),
            FM_MONTHLY_QTY_PBI[SAMPLE_DATE] <= MAXX( ALLSELECTED(FM_MONTHLY_QTY_PBI), FM_MONTHLY_QTY_PBI[SAMPLE_DATE] )
            && FM_MONTHLY_QTY_PBI[TERMINATION_REASON] = "RETI"
        )
    )
return 
    CALCULATE(
        SUM(FM_MONTHLY_QTY_PBI[COLLECTION_LITRES]),
        ID_List,
        DATESINPERIOD(
            'FM_MONTHLY_QTY_PBI'[SAMPLE_DATE],
            MAX('FM_MONTHLY_QTY_PBI'[SAMPLE_DATE]),
            -12,
            MONTH
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Hi @v-kkf-msft 

Thanks you for that. The current month ytd works fine. The alldate ytd is bringing back the same values as the current month.

what i need the measure to do is sum the ytd values for customers who left per month e.g 

in feb 2021, 3 left with a ytd volume of 2,227,056 

in jan 2021, 2 left with a ytd volume of 283,187

in dec 2020, 1 left with a ytd volume of 133,586

I need the measure to sum up the volumes e.g 2,227,056 + 283,187 + 133,586 = 2,643,829 

I can then select a date range and the total will give me the ytd volumes

[v-kkf-msft]

Hi @yaman123 ,

If you want to calculate the total value of the measure [YTD Retirer Volume], try the follow formula:

Total = 
IF(
    ISFILTERED(FM_MONTHLY_QTY_PBI[CustomerID]), 
    [YTD Retirer Volume], 
    SUMX(
        ALLSELECTED(FM_MONTHLY_QTY_PBI[CustomerID]),
        [YTD Retirer Volume]
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Hi @v-kkf-msft 

That does not work. Maybe i am not explaining this properly.

The below is the dataset from a manual spreadsheet. 

In Feb 2021, there were 3 retirers and the ytd volume for these 3 retirers was 2301199. 

In Jan 2021, there was 2 retirers and the ytd volume for these 2 retirers was 283187 and so on... 

I need the YTD Total column in Power BI which is a sum of the YTD Volume for Retirers column. So the total 10,921,614 is the YTD volume from March 20 - Feb 21 from the YTD Volume for Retirers. 

I need the measure to be dynamic, so when i select a date period, it gives me the YTD Total

Hope this helps... 

[v-kkf-msft]

Hi @yaman123 ,

If you want to calculate the total value for 12 months, try the formula:

YTD Total 1 = 
CALCULATE(
    [YTD Retirer Volume],
    DATESINPERIOD(
        'Table'[Supply End Date],
        MAX('Table'[Supply End Date]),
        -12,
        MONTH
    )
)

If you want to calculate the total value for the selected date range, try the formula:

YTD Total 2 = 
var TotalMonth = 
CALCULATE(
    DISTINCTCOUNT('Table'[Supply End Date]),
    ALLSELECTED('Table'[Supply End Date])
)

return
CALCULATE(
    [YTD Retirer Volume],
    DATESINPERIOD(
        'Table'[Supply End Date],
        MAX('Table'[Supply End Date]),
        -TotalMonth,
        MONTH
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

HI @v-kkf-msft 

I need the supply end date to go back 12 months from the max supply end date. E.g if i select 01/02/2021 - 28/02/2021, the total should be from 31/03/2020 - 28/02/2021. We need to select the current month and the YTD to automatically calculate previous 12 months.