Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thampton
Helper III
Helper III

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
Anonymous
Not applicable

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.

 

My solution would be the same as @Anonymous , 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.

Nathaniel_C
Super User
Super User

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!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors