Show value for all months for previous years by default with current year values on graph

[mfikram]

Hi,

I am trying to show values for all months for prior/previous years with the data for current year based on a year slicer. The issue is that the x-axis range always gets dictated  by the most up-to-date values (for the current year). See first screenshot

I have tried several versions of previous year measures based on DAX (using DATEADD/PARALLEL PERIOD, ALLEXCEPT and ALL SELECTED) but cannot make it to show all months for previous years.

Desired output shoud have all the values from 2021 (black line on second image) with the current values for 2022 (black line on first image).

Any help would be appreciated.

[v-yanjiang-msft · Accepted solution]

Hi @mfikram ,

According to your description, I reproduce your problem. As there're only January and Febrary in the year 2021, so when you select 2021 in the slicer, the value of current year only contain January and Febrary, and now the measure previous value will only contain January and Febrary.

Here's my solution.

1.Create aonther month table, which has no relationship with the fact table.

Month = GENERATESERIES(1,12)

2.Create two measures for the value of current year an previous year.

Sales =
CALCULATE (
    MAX ( 'Table'[Sales] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Month] = MAX ( 'Month'[Month] ) )
)

Pre-sales' =
CALCULATE (
    MAX ( 'Table'[Sales] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Year]
            = MAX ( 'Table'[Year] ) - 1
            && 'Table'[Month] = MAX ( 'Month'[Month] )
    )
)

Get the correct result.

I attach my sample for reference.

Best Regards,
Community Support Team _ kalyj

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

[Whitewater100]

Hello:

Here's a screenshot,just using one measure. You can drag your YEAR column into the Filter panel and have 2021 and 2022. The one below I checked off the months I wanted to show how the chart can go over two years or more.

[mfikram]

Thanks,
That makes sense.....however I am interested in showing the graphs as 2 separate trends to show a year over year trend.

[Whitewater100]

OK then all you would do is make a measure for LY and add it to the Values section in the field well.

usually the calculation for LY can be

  LY = CALCULATE[your measure like Sales you have for thi year], DATEADD(Dates[Date]), -1, YEAR))

I assume you have Date table named DATES and a Date Column named Date and this column has been marked as a Date Table.

I hope this helps!

[Whitewater100]

Hi:

Can you make your axis based on Year-Month?

If you put a calculated column in your date table: example with Dates as table name and Date as a coulmn in the date table.

Year-Month CC = FORMAT(Dates[Date], "yyyy-mmm")   or   "yyyymm")

If you don't have any other filter on your visual this would work. Hope this helps!

[mfikram]

Is this what you are saying? Doesn't seem to work at my end

[Whitewater100]

Can you add final ) on measure 🙂