Measure giving wrong answer

[UpharAnand]

Please review the attached 1 screenshot. It is from the visual.

I am doing rolling 6 month sum but it is showing me unexpected result.

Here is the DAX:

TotalUsers_6M =

VAR LATEST_MONTH = MAX(rep_glbl_revenue[order_date]) //CALCULATE(MAX('dimTime'[Latest Date]))

var final_date =

    if(

        ISBLANK(

            year(LATEST_MONTH)

            )

    ,BLANK(), LATEST_MONTH

    )

VAR DATES_IN_PERIOD_TABLE_PY = DATESINPERIOD ('Date'[Fiscal Date], final_date, -6, MONTH)

Return

CALCULATE(

    SUM(rep_glbl_revenue[Net Revenue GBP]),DATES_IN_PERIOD_TABLE_PY)

For the month of July'22, it should sum from Feb22 to Jul22. The correct output should be 986.72 but with the dax function it is showing 1086.55. This is for other dates as well. FIgure is just a sample.

[Alex87 · Accepted solution]

Try this:

TotalUsers_6M = 
VAR _LATEST_MONTH = MAX('Date'[Fiscal Date]) 
Var _END_DATE = EOMONTH(_LATEST_MONTH,0)
VAR _START_DATE = EOMONTH(_END_DATE,-6)+1
VAR _MAXDATE = MAX(Sheet1[order_date])
VAR _VALUE = 
CALCULATE(
    SUM(Sheet1[Net Revenue GBP]),DATESBETWEEN('Date'[Fiscal Date],_START_DATE,_END_DATE))
VAR _RESULT = 
IF( EOMONTH(_MAXDATE,0) >= _LATEST_MONTH, _VALUE)

RETURN _RESULT

[Alex87 · Accepted solution]

It can work with DATESINPERIOD if you protect your date by wrapping it around EOMONTH.

Basically you need to make it clear to the code that needs to take the full period of the month.

I re wrote your logic below.

Debug = 

VAR LATEST_MONTH = MAX(Sheet1[order_date])
Var _END_DATE = EOMONTH(LATEST_MONTH,0)

var final_date =
    if(
        ISBLANK(
            year(_END_DATE)
            )
    ,BLANK(), _END_DATE
    )
VAR DATES_IN_PERIOD_TABLE_PY = DATESINPERIOD ('Date'[Fiscal Date], final_date, -6, MONTH)

VAR _Result = 
CALCULATE(
    SUM(Sheet1[Net Revenue GBP]),DATES_IN_PERIOD_TABLE_PY)

RETURN
_Result

[Alex87 · Accepted solution]

Try this:

TotalUsers_6M = 
VAR _LATEST_MONTH = MAX('Date'[Fiscal Date]) 
Var _END_DATE = EOMONTH(_LATEST_MONTH,0)
VAR _START_DATE = EOMONTH(_END_DATE,-6)+1
VAR _MAXDATE = MAX(Sheet1[order_date])
VAR _VALUE = 
CALCULATE(
    SUM(Sheet1[Net Revenue GBP]),DATESBETWEEN('Date'[Fiscal Date],_START_DATE,_END_DATE))
VAR _RESULT = 
IF( EOMONTH(_MAXDATE,0) >= _LATEST_MONTH, _VALUE)

RETURN _RESULT

[UpharAnand]

Omg, its working now.

You are genious.

I will accept this as a solution.

But before that can you please explain me the calculation ?

VAR _START_DATE = EOMONTH(_END_DATE,-6)+1

Why are you adding 1 in this variable.

Ideally, datesinperiod dax should work but this id the first time I am facing this type of concern

May I know from where you learnt this all ?

[UpharAnand]

But datesinperiod dax should work here. Why it is not working??

Ideally it should work for rolling 6 months calculation

[Alex87]

So, for this variable: "VAR _START_DATE = EOMONTH(_END_DATE,-6)+1"

I am using EOMONTH, which is tranforming my end date to the last day of the month, six month previous.

For June it will therefore be 31/12 and I want it to be 01/01. So I am adding a day (+1). My Start Date Date will always be the first day of the specifc month.

The logic behind the debugging is to cut your logic in smaller parts, use variables, check them if they are correct and easily building the logic until reaching the expected result. 

I am glad it worked! Best regards

[UpharAnand]

Wait a minute, I have one doubt. Like you said for Jun, calculation will start from 31/12 and it will move to 01/01if I use VAR _START_DATE = EOMONTH(_END_DATE,-6)+1. but for jun calculation is already starting from 01/01

[Alex87]

Hello @UpharAnand ,

There is no issue in the solution provided. The DAX calculation is correct as shown in the print screen. Now, you might have some specificities or issues inside your data. I will gladly help if you can provide a sample dummy data that reproduces the issue you are describing. I am unfortunatelly unable to imagine what is the problem without having the data.

Thanks

[UpharAnand]

https://drive.google.com/drive/folders/12LNhJs-WwWQ-_MOXLmWeA83TO59OvsCb Please download the file from the given link @Alex87 

[Alex87]

Hello @UpharAnand ,

I rebuild your datasource and made it work with the following DAX: 

TotalRev_6M = 
VAR LATEST_MONTH = MAX('Dates'[Date]) -- Assuming this is the date dimension table
VAR END_DATE = EOMONTH(LATEST_MONTH, 0) -- Get the end of the latest month
VAR START_DATE = EOMONTH(END_DATE, -6) -- Get the end of the month 6 months before the latest month
RETURN
    CALCULATE(
        SUM(rep_glbl_revenue[Net Revenue GBP]),
        DATESBETWEEN('Dates'[Date], START_DATE, END_DATE)

If it works for you, please mark my reply as the solution. Thanks!

[UpharAnand]

@Alex87 . Its again giving a weird result.Oct23 is the last month of the data availibility for the given UUID but in your dax it showing data till Mar24. It's like its calculating from Oct23 to Mar24 as 6 month rolling. Please review the attached screenshot.

Please help