Year and Month in the same graphic

[GustavoKamchen]

Hello.

I am trying to make a graphic like the one below, with last year, current year, and the last 5 months (April - 4, May - 5, June - 6, July - 7, August - 😎 in the same graphic. How can I achieve this using DAX?

Note: No sensible data were given. These values are AVERAGE per month.

My data:

Year	Month	Sales
2021	1	R$   31.371,81
2021	2	R$   27.398,73
2021	3	R$   30.517,70
2021	4	R$   29.716,61
2021	5	R$   31.885,99
2021	6	R$   29.088,88
2021	7	R$   31.284,70
2021	8	R$   31.291,78
2021	9	R$   30.507,82
2021	10	R$   31.571,20
2021	11	R$   29.567,97
2021	12	R$   31.036,40
2022	1	R$   31.458,14
2022	2	R$   27.969,00
2022	3	R$   30.223,21
2022	4	R$   29.986,37
2022	5	R$   32.006,22
2022	6	R$   29.293,11
2022	7	R$   31.637,75
2022	8	R$   22.377,59

Data for graphic:

Year	Sales
2021	R$   30.436,63
2022	R$   29.368,92
4	R$   29.986,37
5	R$   32.006,22
6	R$   29.293,11
7	R$   31.637,75
8	R$   22.377,59

[v-rongtiep-msft · Accepted solution]

Hi @GustavoKamchen ,

Please refer to my pbix file to see if it helps you.

Create 2 columns.

sales value =
VAR _monthtoday =
    MONTH ( TODAY () )
VAR _yeartoday =
    YEAR ( TODAY () )
VAR _qian5 =
    MONTH ( EOMONTH ( TODAY (), -5 ) )
VAR _aaverage =
    CALCULATE (
        AVERAGE ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Year] = EARLIER ( 'Table'[Year] ) )
    )
RETURN
    IF (
        ( 'Table'[Year] ) = _yeartoday
            && ( 'Table'[Month] ) > _qian5,
        ( 'Table'[Sales] ),
        _aaverage
    )

Columnyear =
VAR _1 =
    CALCULATE (
        COUNT ( 'Table'[sales value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year] = EARLIER ( 'Table'[Year] )
                && 'Table'[sales value] = EARLIER ( 'Table'[sales value] )
        )
    )
VAR _2 = 'Table'[Year]
RETURN
    IF (
        _1 = 1,
        ( 'Table'[Month] ),
        IF ( _1 > 1 && ( 'Table'[Year] ) = _2, ( 'Table'[Year] ), 0 )
    )

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@GustavoKamchen , You have create a new column

New Year Month = If( year([Date]) < Date(Year(Today()),1,1), Year([Date]) , month([Date]))

Sort this on . Select the above column and mark this as sort column

New Year Month Sort = If( year([Date] < Date(Year(Today()),1,1), Year([Date])*100 , Year([Date])*100 + month([Date])

How to Create Sort Column and Solve Related Errors:
https://www.youtube.com/watch?v=KK1zu4MBb-c