cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mfikram
Frequent Visitor

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

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.

2021 vs 2022.jpg2020 vs 2021.jpg

1 ACCEPTED SOLUTION
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1647857874992.png

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.

vkalyjmsft_1-1647857912157.png

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.

 

View solution in original post

7 REPLIES 7
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1647857874992.png

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.

vkalyjmsft_1-1647857912157.png

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
Super User
Super User

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.

 

Whitewater100_0-1647376169244.png

 

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

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
Super User
Super User

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!

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

Can you add final ) on measure 🙂

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors