CY vs. PY Legend

[Anonymous]

I have sample graph attached where I want to compare a scheduled categories for current year vs. prior year. I have a fiscal year slicer on the page though so if a user selects fiscal year 2020 in the slicer the graph should show the CY scheduled category data and then the PY data (2019). Another example, if a user selects fiscal year 2019 in the slicer the graph should show the CY data scheduled category data and then the PY data (2018).

The data model has the scheduled categories in one table, current year data in another table, and prior year data in a separate table. All the data does not exist in one table.

Is this possible with the cluster column chart somehow? or at least something similar to the sample graph that conveys the same message? Or would it involve a new DAX calculation?

[amitchandak]

@Anonymous , This is where you need to have a measure to dimension conversion, That you need to using Union and sumamrize.

I am sharing an example see if that can help

As this will an independent table add any filter you need as slicer or you want to join back

union(
SUMMARIZE('Table', 'Table'[any Group by col], "Engagement","Engaged","CountEngagement" , sum('Table'[Engaged])),
SUMMARIZE('Table', 'Table'[any Group by col], "Engagement","Disengaged","CountEngagement" , sum('Table'[Disengaged])),
SUMMARIZE('Table', 'Table'[any Group by col], "Engagement","ActivelyDisengaged","CountEngagement" , sum('Table'[ActivelyDisengaged]))
)


union(
SUMMARIZE('Table', "Engagement","Engaged","CountEngagement" , sum('Table'[Engaged])),
SUMMARIZE('Table', "Engagement","Disengaged","CountEngagement" , sum('Table'[Disengaged])),
SUMMARIZE('Table', "Engagement","ActivelyDisengaged","CountEngagement" , sum('Table'[ActivelyDisengaged]))
)

[Anonymous]

I ended up creating a calculated column in my dimDate table

Current Year Calculation = IF(YEAR('dim Date'[Date])=YEAR(TODAY()),"Current Year",CONVERT(YEAR('dim Date'[Date]),STRING))

Prior Year Calculation = IF(YEAR('dim Date'[Date])=YEAR(TODAY())-1,"Prior Year",CONVERT(YEAR('dim Date'[Date]),STRING))

This tags everything in my dimDate table with this year as Current Year and Prior Year, but I was having troubles figuring out how to incorporate an OR statement to combine both of these calculations into one. If I can do that then I can use the combined field in my legend which will then show CY vs. PY.

[V-lianl-msft]

Hi @Anonymous ,

Please try:

SWITCH(TRUE(),
YEAR('dim Date'[Date])=YEAR(TODAY()),"Current Year",
YEAR('dim Date'[Date])=YEAR(TODAY())-1,"Prior Year",
CONVERT(YEAR('dim Date'[Date]),STRING)
)

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