Clusterd Column Chart: how to get full year and a month next together

[Anonymous]

Hi,

In my report, I have a clustered column chart like the one below. The green bar displays values for 2018 (full year) and the black one shows data for 2019 YTD.

I would like to be able to show a single month as an additional third column to eventually come to something like this:

The legend is built on a column computed with:

Legend = IF('TestData'[Year] = "2018" ; "2018 FY" ; IF('TestData'[Year] = "2019" && 'TestData'[Month] = "5" ; "2019.05" ; IF('TestData'[Year] = "2019" ; "2019 YTD" ; 'TestData'[Year])))

But the way I managed to do it now, isn't exactly what I need. The black column isn't showing all the data for 2019 anymore since May is taken out of it and displayed in the yellow column. 

Is there a way to get it right? Where green is 2018 (full year), black is 2019 YTD and yellow is the last full month available?

Additionally, it would be great if there could be a way that this last full month could change automatically. Meaning that nobody has to go in manually to change the month of the yellow column. Today, the yellow column should display May. As of the 1st of July, the yellow column should display June.

Hope this makes sense.

Thanks,

Annelies

[v-shex-msft]

Hi @Anonymous ,

You can try to use below calculated column formula if it suitable for your requirement:

Legend =
VAR _lastYear =
    MAXX ( ALL ( TestData ); [Year] )
VAR _lastMonth =
    MAXX ( ALL ( TestData ); [Month] )
RETURN
    IF (
        'TestData'[Year] = _lastYear - 1;
        [Year] & " FY";
        IF (
            'TestData'[Year] = _lastYear
                && 'TestData'[Month] = _lastMonth;
            [Year] & "." & [Month];
            IF ( 'TestData'[Year] = _lastYear; [Year] & "2019 YTD"; 'TestData'[Year] )
        )
    )

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

Thank you for your reply. It's always great to learn new DAX-code.

I fully understand the logic behind it, but I can't get it to work though. It gives me the following error message: "Expressions that yield variant data-type cannot be used to define calculated columns."

I figured the cause might be that we're adding text to a number, so I tried changing the data type for [Year] and [Month] to text. After which I get this: "DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE of FORMAT function to convert one of the values." Probably due to the "_lastYear - 1" part?

Any ideas on how to solve this?

Hope this works as sample data:

Date	Year	Month	Category	Value
1/01/2018	2018	1	Bleu	10
20/01/2018	2018	1	Red	5
24/01/2018	2018	1	Bleu	3
1/02/2018	2018	2	Green	3
20/02/2018	2018	2	Purple	10
24/02/2018	2018	2	Red	1
1/03/2018	2018	3	Bleu	6
20/03/2018	2018	3	Green	2
24/03/2018	2018	3	Purple	2
1/04/2018	2018	4	Purple	8
20/04/2018	2018	4	Red	9
24/04/2018	2018	4	Green	2
1/05/2018	2018	5	Bleu	4
20/05/2018	2018	5	Bleu	1
24/05/2018	2018	5	Green	6
1/06/2018	2018	6	Purple	6
20/06/2018	2018	6	Purple	3
24/06/2018	2018	6	Red	7
1/07/2018	2018	7	Green	8
20/07/2018	2018	7	Red	6
24/07/2018	2018	7	Bleu	1
1/08/2018	2018	8	Red	2
20/08/2018	2018	8	Purple	9
24/08/2018	2018	8	Green	4
1/09/2018	2018	9	Purple	8
20/09/2018	2018	9	Bleu	2
24/09/2018	2018	9	Green	7
1/10/2018	2018	10	Purple	10
20/10/2018	2018	10	Red	2
24/10/2018	2018	10	Bleu	4
1/11/2018	2018	11	Green	5
20/11/2018	2018	11	Purple	9
24/11/2018	2018	11	Purple	5
1/12/2018	2018	12	Red	8
20/12/2018	2018	12	Red	1
24/12/2018	2018	12	Red	6
1/01/2019	2019	1	Green	10
20/01/2019	2019	1	Green	9
24/01/2019	2019	1	Bleu	8
1/02/2019	2019	2	Purple	10
20/02/2019	2019	2	Purple	2
24/02/2019	2019	2	Bleu	8
1/03/2019	2019	3	Bleu	2
20/03/2019	2019	3	Green	3
24/03/2019	2019	3	Red	1
1/04/2019	2019	4	Purple	3
20/04/2019	2019	4	Purple	6
24/04/2019	2019	4	Green	10
1/05/2019	2019	5	Bleu	6
20/05/2019	2019	5	Red	3
24/05/2019	2019	5	Green	6
1/06/2019	2019	6	Red	8
20/06/2019	2019	6	Bleu	6
24/06/2019	2019	6	Purple	9

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

Based on test, I found my formula return multiple data types(number and text) that power bi can't recognize column data type. I modify my formula to change output data type to text to fix this issue:

Legend = 
VAR _lastYear =
    MAXX ( ALL ( Table1 ), [Year] )
VAR _lastMonth =
    MAXX ( ALL ( Table1 ), [Month] )
RETURN
    IF (
        'Table1'[Year] = _lastYear - 1,
        [Year] & " FY",
        IF (
            'Table1'[Year] = _lastYear
                && 'Table1'[Month] = _lastMonth,
            [Year] & "." & [Month],
            IF ( 'Table1'[Year] = _lastYear, [Year] & "2019 YTD", 'Table1'[Year]&"" )
        )
    )

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

It works perfectly, thank you very much!

Cheers,

Annelies