Current and Previous Year Cumulative

[Syzmik]

Hi,

I'm wanting to display current year (last 12 calendar months) and previous year (months 24-13) cumulative job count on a line graph to visualise the difference in jobs YOY.

I can get monthly numbers as a SUM to appear per month with SAMEPERIOSLASTYEAR, but struggling producing a cumulative total with similar time logic. This is a sample set of data I have. 

• Jobs represents No. jobs for the month
• CY is the cumulative total for the previous 12 months that I've added together in Excel for reference

• Last year is same logic as above for months 24-13 from current date

• Haven't included Apr-24 in the CY calculation as wanting this for calendar months

Mth/Yr     Jobs     CY    Last year 

Apr-22	999		999
May-22	1149		2148
Jun-22	856		3004
Jul-22	925		3929
Aug-22	1036		4965
Sep-22	1264		6229
Oct-22	994		7223
Nov-22	1258		8481
Dec-22	1001		9482
Jan-23	946		10428
Feb-23	718		11146
Mar-23	889		12035
Apr-23	763	763
May-23	882	1645
Jun-23	1029	2674
Jul-23	872	3546
Aug-23	1115	4661
Sep-23	956	5617
Oct-23	852	6469
Nov-23	864	7333
Dec-23	663	7996
Jan-24	655	8651
Feb-24	701	9352
Mar-24	511	9863
Apr-24	324

I'm trying to get it into a format like

Month    CY    LY 

Apr	763	999
May	1645	2148
Jun	2674	3004
Jul	3546	3929
Aug	4661	4965
Sep	5617	6229
Oct	6469	7223
Nov	7333	8481
Dec	7996	9482
Jan	8651	10428
Feb	9352	11146
Mar	9863	12035

[v-xinruzhu-msft]

Hi, 

Thanks for the solution @Ashish_Mathur  and @amitchandak  provided, i want to offer some more information for user to refer to.

hello @Syzmik , you can refer to the following solution.

1.There is no relationship between date table and fact table.

2.Create the followign measures.

CY =
VAR a =
    MAXX ( ALLSELECTED ( 'Table 2' ), [Date] )
VAR b =
    EOMONTH ( a, -13 ) + 1
VAR c =
    EOMONTH ( a, -1 )
RETURN
    IF (
        MAX ( 'Table'[Mth/Yr] ) >= b
            && MAX ( 'Table'[Mth/Yr] ) <= c,
        CALCULATE (
            SUM ( 'Table'[Jobs] ),
            ALL ( 'Table' ),
            'Table'[Mth/Yr] <= MAX ( 'Table'[Mth/Yr] ),
            'Table'[Mth/Yr] >= b,
            'Table'[Mth/Yr] <= c
        )
    )

LY =
VAR a =
    MAXX ( ALLSELECTED ( 'Table 2' ), [Date] )
VAR b =
    EOMONTH ( a, -25 ) + 1
VAR c =
    EOMONTH ( a, -13 )
RETURN
    IF (
        MAX ( 'Table'[Mth/Yr] ) >= b
            && MAX ( 'Table'[Mth/Yr] ) <= c,
        CALCULATE (
            SUM ( 'Table'[Jobs] ),
            ALL ( 'Table' ),
            'Table'[Mth/Yr] <= MAX ( 'Table'[Mth/Yr] ),
            'Table'[Mth/Yr] >= b,
            'Table'[Mth/Yr] <= c
        )
    )

Output

Best Regards!

Yolo Zhu

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

[Syzmik]

Thanks for the replies.

I ideally want to be able to use a page level date slicer to affect this. For example, Last 1 year should result in selections from April 2023 - March 2024 for CY, and April 2022 - March 2023. Next month (May), I'd need the calculations to move forward a month (May - April).

I'm assuming the "31/3" logic in the forumlas is hard-coding the date to always display March - April?

[Ashish_Mathur]

HI,

PBI file attached.

Hope this helps.

[amitchandak]

@Syzmik , I think you need YTD based on March as the end of the year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))