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 month, but selecting "in 2 calendar months" tallies the total of both months. I am looking to separate the month on its own.

 

For example, 

 

* Current Month (eg Nov 2023)- I am using relative date "in this month"

* Next Month (Current month + 1: Dec 2023) - I am using relative date "in the next 1 calendar months"

* Next Month +2 (Jan 2024) - How do I capture this month on its own?

 

For reference, I am using the following measure to calculate the total values and this works well with the relative date filters also:

 

CPS Monthly =
CALCULATE(
    SUM('Finance Look Ahead Tabulated'[Value]),
    USERELATIONSHIP('Calendar'[Date], 'Finance Look Ahead Tabulated'[Date]))
 
Any assistance is appreciated.

 

 

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))

3 Replies

  • 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)

  • kristi_in_heels's avatar
    kristi_in_heels
    Icon for Helper II rankHelper II
    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))