cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thampton Member
Member

Looking backs to prior months DAX

I have a table with two columns, one for Month and one for Year. I have created a summary table based on connected tables, but need to create columns based on month/year combination. For example, one column will show a sum of a value for 3 months prior, 6 months prior, etc...

 

My question is how can i create a FILTER that takes the month / year columns into considertation. 

 

For example, I want to see 10 months back from the current day. The way i have it now is, Month(utcnow()) - 10, which equals -1. I need this to equal 12 (December). It would also need to account for the year being different. 

 

 

3 REPLIES 3
Super User IV
Super User IV

Re: Looking backs to prior months DAX

Hi @thampton ,

Just did this yesterday for another poster. In Power Query, set up a column for today.

 Table.AddColumn(#"Added Conditional Column1", "Custom.2", each DateTime.Date(DateTime.LocalNow()))

That gives you a column with today in every row.

Then a column that references today but in this case 3 months back

= Table.AddColumn(#"Added Conditional Column2", "Custom.5", each Date.AddMonths(Date.From([Custom.2]), Number.From(-3))),

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




svoves Regular Visitor
Regular Visitor

Re: Looking backs to prior months DAX

It looks like @Nathaniel_C  set you up with a solution using M. Do you have a date table set up? Might be useful to have for this time intellegence type stuff. You could have a column in your date table that looks something like this:

 

"YearMonthNumber", FORMAT ( [Date], "YYYY.MM" )

Then, as long as all your tables are related to the date table, you can use the date table to establish your filters. Key field should just be date.

 

Then you can adjust your times using DATEADD 

Sales minus 3 months =CALCULATE([Sales], DATEADD(DateTable[Date],-3, MONTH))

 

Kind of a hodge podge answer from me but if it works, please mark it as a solution or let me know if you have more questions on this technique.

 

phyberoptx Frequent Visitor
Frequent Visitor

Re: Looking backs to prior months DAX

My solution would be the same as @svoves , but I would first setup some convenience filters.

 

Setup a bunch of convenience Measures that output TRUE or FALSE like:

Rolling 10 Months =
IF (
Dates[PK_Date] > EDATE ( TODAY ()-10 )
&& ( Dates[PK_Date] <= EDATE ( TODAY ()0 ) ),
    “TRUE”,
    “FALSE”
)

Do these for each of the different time intelligence periods you might want.

Then, for each of your measures used to set your summary table information, they are more legible:

Sales Rolling 10 Months =CALCULATE([Sales], [Rolling 10 Months]="TRUE")  

 The advantage here is that the convenience filters aren't just beneficial to your summary table. They also help in other visuals, as you can add the filter to a visual containing a Measure, make it so the data is filtered to where [Convenience Filter] = "TRUE", and automagically restrict the dates used in that visual.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors