Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Get next month data based on selected month

Hi Guys,

In my report the requirement is I need to get sum of weeks based on selected month but the twist is I need to get next month sum of weeks. 
for e.g. If user selected May from Month filter it needs to show data for Jun and if user select Jun in month filter it needs to show July data etc.

Below is my DAX 

Selected Month + 1 =
VAR _Date1 = DATE(SELECTEDVALUE('Date'[Year]),SELECTEDVALUE('Date'[MonthOfYear])+ 1,1)
VAR _Date2 = DATE(SELECTEDVALUE('Date'[Year]),SELECTEDVALUE('Date'[MonthOfYear])+ 1,27)
RETURN
CALCULATE(
SUMX(ListingDelays_Response_Updated, ListingDelays_Response_Updated[Number of weeks]),
ListingDelays_Response_Updated[EnteredDate] >= _Date1 &&
ListingDelays_Response_Updated[EnteredDate] <= _Date2 )
 
Now if I remove +1 from SELECTEDVALUE('Date'[MonthOfYear])+ 1 it is showing data of selected month but no result are shown with + 1. I checked the VAR is seperate measure it is showing correct dates.
 
Any suggestion what I doing wrong and how to acheive above?
 
Thanks 
 
DAX
power bi
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    Hi Anonymous 

    The first thing to note is you'll have a problem if the user selects December.  Your logic to add 1 to the month number (MonthOfYear) will give you month 13.

    DAX has a function called DATEADD which is commonly used in scenarios like this.

    Something like:

    SelectedMonth + 1 = 
CALCULATE(
    SUM(ListingDelays_Response_Updated[Number of weeks]),
    DATEADD('Date', 1, MONTH)
    )

     

2 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous 

    The first thing to note is you'll have a problem if the user selects December.  Your logic to add 1 to the month number (MonthOfYear) will give you month 13.

    DAX has a function called DATEADD which is commonly used in scenarios like this.

    Something like:

    SelectedMonth + 1 = 
CALCULATE(
    SUM(ListingDelays_Response_Updated[Number of weeks]),
    DATEADD('Date', 1, MONTH)
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi PaulOlding,

       

      Thanks for this, DateADD actually really helped. 

      Best Regards,

      Thanks