Forum Discussion

kristi_in_heels's avatar
kristi_in_heels
Icon for Helper II rankHelper II
2 years ago
Solved

Help with Relative Dates & Measure for 2 Months Time excluding next month

Hello,   I am trying to create a visual using cards, which will return 3 separate values for this month, next month, and the month after that.   Using relative dates works for this month and next...
Need Help
  • kristi_in_heels's avatar
    kristi_in_heels
    2 years ago

    I have managed to use your format to create a measure which is working for +2 Months, thank you for your help.

     

    CPS This Month +2 =
    var _min = eomonth(today(),0)+2
    var _max = eomonth(today(),2)
    return CALCULATE([CPS Monthly], FILTER('Calendar','Calendar'[Date] >=_min && 'Calendar'[Date] <= _max))
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

kristi_in_heels , if you want without filter

 

This Month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

Last Month Today =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

 

Next Month Today =
var _min = eomonth(today(),0)+1
var _max = eomonth(today(),1)
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

use all(Date) to igonre filter

This Month Today =
var _min = eomonth(today(),-1)+1
var _max = eomonth(today(),0)
return CALCULATE([Net], FILTER(all('Date') ,'Date'[Date] >=_min && 'Date'[Date] <= _max))

 

 

With the filter of slicer, you can use measures like

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

 

 

 

 

  • kristi_in_heels's avatar
    kristi_in_heels
    Icon for Helper II rankHelper II
    2 years ago

    Thank you, I can't see where the today + 2 months measure comes in?

     

    Essentially I need something that will return the total of every value for January 2024 if I run the numbers today (Nov-2023)