Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
UpharAnand
Helper I
Helper I

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)

UpharAnand_1-1716210375065.png

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.

2 ACCEPTED SOLUTIONS
Alex87
Solution Sage
Solution Sage

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

View solution in original post

Alex87
Solution Sage
Solution Sage

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

 

View solution in original post

10 REPLIES 10
Alex87
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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

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 ?

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

 

Ideally it should work for rolling 6 months calculation

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

 

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
Solution Sage
Solution Sage

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

Alex87
Solution Sage
Solution Sage

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)

 

 

Alex87_0-1716215379928.png

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

@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.

UpharAnand_0-1716272138116.png

Please help

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors