Get monthly figures from financial YTD

[aquan]

Hello

I have a list of YTD figures with the YTD ending on 30-June each year.

The data is uploaded monthly.

The below measure works for every month except July (which would require adjustment). How would I adjust the measure so that in the July month only, it doesn't take away June. I want the measure to work across multiple years.

var CurrentYTD = sum(Financial[Total Actual])

var PriorMonthYTD = CALCULATE(SUM(Financial[Total Actual]),PARALLELPERIOD('Calendar'[Date],-1,MONTH))



return
(CurrentYTD - PriorMonthYTD)

[Stachu]

does this work as you expect?

Measure =
VAR __CurrentYTD =
    SUM ( Financial[Total Actual] )
VAR __PriorMonthYTD =
    IF (
        MONTH ( MAX ( 'Calendar'[Date] ) ) = 7,
        0,
        CALCULATE (
            SUM ( Financial[Total Actual] ),
            PARALLELPERIOD ( 'Calendar'[Date], -1, MONTH )
        )
    )
RETURN
    ( __CurrentYTD - __PriorMonthYTD )

if not, can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[aquan]

I had tried that also. But this only works if you only select 1 month.

If you select a range of months then 

MAX ( 'Calendar'[Date])

 will no longer be max (if i understand correctly)

If you have another solution using "M" that would be even better.

		Cumulative		Period
1/05/2016	31/05/2016	7			7
1/06/2016	30/06/2016	9			2
1/07/2016	31/07/2016	2			2
1/08/2016	31/08/2016	4			2
1/09/2016	30/09/2016	6			2
1/10/2016	31/10/2016	9			3
1/11/2016	30/11/2016	10			1
1/12/2016	31/12/2016	16			6
1/01/2017	31/01/2017	25			9
1/02/2017	28/02/2017	35			10
1/03/2017	31/03/2017	45			10
1/04/2017	30/04/2017	54			9
1/05/2017	31/05/2017	63			9
1/06/2017	30/06/2017	65			2
1/07/2017	31/07/2017	8			8
1/08/2017	31/08/2017	10			2
1/09/2017	30/09/2017	19			9
1/10/2017	31/10/2017	23			4
1/11/2017	30/11/2017	29			6
1/12/2017	31/12/2017	35			6
1/01/2018	31/01/2018	40			5
1/02/2018	28/02/2018	49			9
1/03/2018	31/03/2018	58			9
1/04/2018	30/04/2018	63			5
1/05/2018	31/05/2018	71			8
1/06/2018	30/06/2018	73			2

[Stachu]

I thought your values are cumulative, so if you would have double counting for the overlapping periods. If that's not the case, then why not use DATESYTD with custom year end to get the YTD, e.g.

Sales = SUM(Financial[Total Actual])

Sales YTD = CALCULATE([Sales], DATESYTD(Calendar[Date], "30-06")

that way the Sales YTD will give you the YTD figure even with 1 month selected, slightly different navigation logic, but much easier to implement

[aquan]

Yes - they are cumulative-financial year to date.

Ok so get the YTD then offset the financial year by 1 month then subtract... DATESYTD I'll give it a try. Thanks.