Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Measure giving wrong answer

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.

Need Help
  • Alex87's avatar
    Alex87
    2 years ago

    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's avatar
    Alex87
    2 years ago

    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

     

10 Replies

  • Alex87's avatar
    Alex87
    Solution Sage
    2 years ago

    Hello Anonymous ,

     

    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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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

  • Alex87's avatar
    Alex87
    Solution Sage
    2 years ago

    Hello Anonymous ,

    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

  • Alex87's avatar
    Alex87
    Solution Sage
    2 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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 ?

      • Alex87's avatar
        Alex87
        Solution Sage
        2 years ago

        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

         

  • Alex87's avatar
    Alex87
    Solution Sage
    2 years ago

    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