Forum Discussion

Syzmik's avatar
Syzmik
Frequent Visitor
2 years ago

Current and Previous Year Cumulative

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-22999 999
May-221149 2148
Jun-22856 3004
Jul-22925 3929
Aug-221036 4965
Sep-221264 6229
Oct-22994 7223
Nov-221258 8481
Dec-221001 9482
Jan-23946 10428
Feb-23718 11146
Mar-23889 12035
Apr-23763763 
May-238821645 
Jun-2310292674 
Jul-238723546 
Aug-2311154661 
Sep-239565617 
Oct-238526469 
Nov-238647333 
Dec-236637996 
Jan-246558651 
Feb-247019352 
Mar-245119863 
Apr-24324  

 

I'm trying to get it into a format like

Month    CY    LY 

Apr763999
May16452148
Jun26743004
Jul35463929
Aug46614965
Sep56176229
Oct64697223
Nov73338481
Dec79969482
Jan865110428
Feb935211146
Mar986312035
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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"))

  • Syzmik's avatar
    Syzmik
    Frequent Visitor
    2 years ago

    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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.