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 month...
Need Help
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.