How to add 1 month to the quarter in calculated measure?

[k3rz0rg]

Hi all , 

I have a calculation that is doing sum of sales quarterly and I was using QUARTER([date]) in where condition, ie:
calculate(
                  Sum([Sales])
                  ,YEAR([date])-1 = YEAR([enddate])-1 --Looking at last year
                  ,QUARTER([date]) <= QUARTER([enddate]) --comparing quarter to quarter
)
now I wanted to add one month to that QUARTER([enddate]) , can somebody help me to do so?
Adding + 1 adds another quarter to the enddate so definitely can't use that there... 

Thanks.

[Ray_Minds]

Please provide us some sample data with your expected result.

[Awm]

Here is the modified version of your existing DAX formula:

QuarterSales = 
CALCULATE(
    SUM([Sales]),
    YEAR([date]) - 1 = YEAR([enddate]) - 1, -- Looking at last year
    QUARTER([date]) <= QUARTER(EDATE([enddate], 1)) -- Comparing quarter to adjusted quarter
)

In this approach, EDATE([enddate], 1) adds one month to the enddate, and QUARTER determines the new quarter based on this adjusted date.
Hope it helps!

Best regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sroy_16]

Try using this DAX cide,

Sales QTD Adjusted =
VAR AdjustedEndDate = EOMONTH([enddate], 1)
VAR AdjustedQuarter = QUARTER(AdjustedEndDate)

RETURN
CALCULATE(
SUM([Sales]),
YEAR([date]) = YEAR([enddate]) - 1,
QUARTER([date]) <= AdjustedQuarter
)


Let me know if this solution works.

[k3rz0rg]

Hi @sroy_16 , thank you for your prompt response, I've tried that and weirdly, it's adding 2 months of sales instead of 1 month, I went through the documentation for EOMONTH and your one should work by adding 1 month, not sure why it's adding an extra month... Trying some more tweaks on this.