topic Re: Show value for all months for previous years by default with current year values on graph in DAX Commands and Tips

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

mfikram — Tue, 15 Mar 2022 18:24:20 GMT

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.

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

Whitewater100 — Tue, 15 Mar 2022 18:52:06 GMT

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!

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

mfikram — Tue, 15 Mar 2022 19:14:51 GMT

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

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

Whitewater100 — Tue, 15 Mar 2022 20:30:27 GMT

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.

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

mfikram — Wed, 16 Mar 2022 14:41:56 GMT

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

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

Whitewater100 — Wed, 16 Mar 2022 15:41:54 GMT

Can you add final ) on measure 🙂

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

Whitewater100 — Wed, 16 Mar 2022 15:46:14 GMT

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!

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

v-yanjiang-msft — Mon, 21 Mar 2022 10:19:31 GMT

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.