Issues with Month[today]

[Libbyb23]

I have a formula:

6month Prior = CALCULATE(SUM('Sales Data'[sales_price]),  FILTER('Sales Data','Sales Data'[Month Match] = true && 'Sales Data'[Month if Matching] = [Current Month]-6))/ CALCULATE([# of WorkDays], FILTER('Date Key', 'Date Key'[Month] = [Current Month] -6))

However, this returns blank and its due to my [Current Month] measure. This measure is 

Current Month = MONTH(TODAY()).

This will allow me to go back to 5 months prior, but will not at 6 months and I think it is due to the fact it will bleed into 2023, which is what I need. I am not sure to fix this.

[Greg_Deckler · Accepted solution]

@Libbyb23 I would recommend using EOMONTH. You can use a pattern such as:

Measure =
  VAR __Today = TODAY()
  VAR __Max = EOMONTH( __Today, -6 )
  VAR __Min = DATE( YEAR( __EOM6 ), MONTH( __EOM6 ), 1 )
  VAR __Table = FILTER( ALL( 'Sales Data' ), [Date] >= __Min && [Date] <= __Max )
  VAR __Result = SUMX( __Table, [sales price] )
RETURN
  __Result

Or do you not have a Date column?

[Libbyb23]

@Greg_Deckler this worked perfectly!! How would I update this to count working days for those time frames?

[Greg_Deckler]

@Libbyb23 There is a NETWORKDAYS function in DAX now, or the old school way: Net Work Days - Microsoft Fabric Community

[Libbyb23]

@Greg_Deckler 

What is the __EOMO6?

[Libbyb23]

@Greg_Deckler I do have a date column! I will try this out today and get back to you.

[michaelu1]

@Libbyb23 Perhaps use a month offset column which has a 0 for the current month and adds/subtracts 1 for every month forward/back

[Libbyb23]

Could you send me an example?

[michaelu1]

I use this in power Query:

#"Added Custom1" = Table.AddColumn(#"Inserted Quarter", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),

[Libbyb23]

Hi, 

I am not sure which table I should put this. Should I do it on my date table or on my sales table?

[michaelu1]

I have it on my date table.

You may also want to look into using the DATEADD formula..

[Libbyb23]

[Libbyb23]

Hmm, its not working for me. I have tried the DATEADD with no success. Any ideas?

[Libbyb23]

OKay thanks! I am trying it out now

[Libbyb23]

I just figured it out, but still need help with a solution. 

6month Prior = CALCULATE(SUM('Sales Data'[sales_price]),  FILTER('Sales Data','Sales Data'[Month Match] = true && 'Sales Data'[Month if Matching] = [Current Month]-6))/ CALCULATE([# of WorkDays], FILTER('Date Key', 'Date Key'[Month] = [Current Month] -6))

it works except for 6+ months prior because we are in June (month 6) -6 is 0. But I need to now go back to 12/2023. Any ideas are appreciated!!