Previous years sum by year and current year sum by quarter in the same clustered column chart

[Mikazuki]

Hello there,

suppose I have a dataset like:

Fruit	Time
Apple	2021/01/23
Apple	2022/05/23
Pear	2023/01/01
Watermelon	2021/01/26
Apple	2022/01/26
Pear	2022/03/26
Watermelon	2023/03/26
Pear	2023/06/26
Apple	2023/08/26
Pear	2023/12/26

Is it possible that I can have a clustered column chart like this to show the count of fruit by year if is before 2023 and quarter if is in 2023(Ignore the numbers lol):

Thanks for your help!!!

[PawarNovil · Accepted solution]

Hi @Mikazuki ,

Please try below steps- 

1. create a KPI grouping table-

KPIGrouping = DATATABLE( "KPI", STRING,

                        "Sort", INTEGER,

                         { {"2021", 1 },

                          {"2022",2},

                          {"YTD2023",3},

                          {"1st Quarter",4},

                          {"2nd Quarter",5},

                          {"3rd Quarter",6},

                          {"4th Quarter", 7}

                          }

)

------------------------------------------------------

2. create measures for your individual KPI's (2021, 2022, YTD2023,....) based on your required result and filter context.
for example -
Count2021 = CALCULATE(COUNTA(FruitTable[Fruit]),  year(FruitTable[Date])=2021)

Count2022=CALCULATE(DISTINCTCOUNT(FruitTable[Fruit]), FILTER(FruitTable, year(FruitTable[Date])=2022))

--------------------------------------------------------

3.  create visual and use KPI  from KPIGrouping table and cretae a measure as below

-----------------------

KPI Grouping Cluster Chart =

var selectedKPI=SELECTEDVALUE(KPIGrouping[KPI],"2021")

var currentkpi = switch (

selectedKPI,

"2021", [Count2021],

"2022",[Count2022],

"YTD2023",[Count2023YTD],

 "1st Quarter",2,  // Put your 1st Quarter measure name in place of 2

"2nd Quarter",1, // Put your 2nd Quarter measure name in place of 1

"3rd Quarter",1,  // Put your 3rd Quarter measure name in place of 1

"4th Quarter", 1, // Put your 4th Quarter measure name in place of 1

blank()

)

return

currentkpi

---------------------------------------------------------------------------------------------

 

--------------------------------------------------------------------------------------------------------------------

Regards,

Novil

If I answer your question, please mark my post as a solution.

[Mikazuki]

Dear Pat,

I see the link that you attached but I still cannot get the data like 2023YTD as an individual part from the graph, could you please help me with the dax function with the sample data that I listed before? Thanks.

[ppm1]

You can do that with calculation groups.

Introducing Calculation Groups - SQLBI

Pat