I need help writing dax for my x axis displaying 3 years back “total sum”, month by month (see example pic). I can use a Relative Slicer, but that will still show the current month (May 2021) with partial 2018 because I have it set for (Last 3 Years). Thanks for any help!!!
Hi @LisaKBI1129. Please upload you work-in-progress PBIX file and your sample dataset for the forum members to review in their analyses.
Greg
this sort of visual is a snap with the @Melissa’s great Calendar code.
take a look at the offset columns, with these you can set a visual (or measure) to include all months within a range of offset months.
if you don’t want to replace your entire calendar table (although this is a really robust table and I do advise it), simply add the needed offset columns.
Thanks Heather. @Melissa has always helped me with dax that I need help with. Do you know how to write the dax for this after I’ve replaced my data table with Melissa’s data table including the offset column?
.
You can replace the Relative Date Slicer with a MonthOffset, doesn’t require any additional coding.
Just select the time frame you want and when the Date table code is refreshed it automatically updates. The current month is always equal to MonthOffset 0. So next month (Jun-21) will be equal to MonthOffset 0 and so on.
.
I hope this is helpful.
Are you saying to click and drag the “MonthOffset” column to the Filter pane? I’m not sure where to put it. This tab won’t have any Year, Month or Date Slicer on it. Can you create it on my pbix file I created and repost?
Hi @LisaKBI1129,
I’m working with a newer version of PBI so that won’t do you any good but here are the steps.
Update the date table.
.
Quick and dirty solution, just place the MonthOffset in the Filter Pane
.
Or a DAX alternative, something like.
Count Pages = COUNT( 'Data source'[Total Pages] )
I hope this is helpful.
1 Like
Hi LisaKBI1129,
I made an alternative workout with DAX formulas, based upon the DateAdd formula, with my own dummy actual sales data, which goes till May 2021.
The measure " 3 years ago " contains an extra condition, to maintain 3 years reporting for every column. You do not need filters, you can select different years with the slicer.
A disadvantage is that the X-axis can not present the years per month (like 2018/2019/2020), as the values are based upon measures.
See the PBIX Rolling sales per month v3 .pbix (152.7 KB)
It is possible that Melissa’s solution suits you/is better, I am looking forward for any comments.
Measure " 3 years ago"
A visual:
1 Like