Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Issues with Month[today]

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))/ CALCUL...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    Anonymous 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?

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Could you send me an example?

michaelu1's avatar
michaelu1
Advocate II
2 years ago

I use this in power Query:

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    OKay thanks! I am trying it out now

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      michaelu1
      Advocate II
      2 years ago

      I have it on my date table.

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

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