cancel
Showing results 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

Helper I

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.

2 ACCEPTED SOLUTIONS
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``````
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``````

10 REPLIES 10
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``````

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``````
Helper I

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 ?

Helper I

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

Ideally it should work for rolling 6 months calculation

Solution Sage

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

Helper I

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

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

Helper I
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)``````

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

Helper I

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors