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

Frequent Visitor

## minx - one month

Hi

I'm building a measure, and I can't seem to get my Var's quite right

I need to hunt for values in the last three months and then later in the IF statement hunt in everything before that three-month period

var LastThreeMonths = DATESINPERIOD(dim_calendar[Date], max(dim_calendar[MAXFilter]),-2,MONTH)
var PreviousMonths = DATESBETWEEN(dim_calendar[Date],blank(),minx(LastThreeMonths, dim_calendar[Date]))

My issue is I have the same month in both (i.e. the earliest date in LastThreeMonths will obviously be in minx of LastThreeMonths in the 2nd VAR)

I know this is whats happening but how can I move ,minx(LastThreeMonths, dim_calendar[Date])) back a month?

TIA

2 ACCEPTED SOLUTIONS
Super User

Last three month

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH))

Everything before three month

LTD(complete till date) =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-3)
var _min = minx(all('Date'), 'Date'[Date]) // or minx(allselected('Date'), 'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Using window

Rolling 3 = CALCULATE([Net], WINDOW(-2,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Using window

Rolling before 3 = CALCULATE([Net], WINDOW(0,ABS, -3, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

Super User

Hi,

Does this pattern work?

Sales for 3 months ended select month = calculate([Total sales],datesbetween(calendar[date],edate(min(calendar[date]),-2),max(calendar[date])))

Sales in 3 months prior = calculate([Sales for 3 months ended select month],datesbetween(calendar[date],edate(min(calendar[date],-5),edate(min(calendar[date],-2))))

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Community Support

According to your description, minx(LastThreeMonths, dim_calendar[Date])) seeks the value of the date three months ago, for example, it is now February 19, this formula seeks November 19, 2023. the second parameter in DATESBETWEEN is blank(), which means the minimum date in the table, in my table The second parameter in DATESBETWEEN is blank(), indicating the minimum date in the table, which in my case is November 2, 2023.

The following dax expression can be used to find the value of the date three months ago:

``````Measure =

var _maxdate=

MAXX(ALL('Table'),'Table'[Date])

var _last3month=

DATE(

YEAR(_maxdate),MONTH(_maxdate)-3,DAY(_maxdate))

return

_last3month``````

The result at this point is shown below:

On the basis of this, you can change this expression to

``````Measure =

var _maxdate=

MAXX(ALL('Table'),'Table'[Date])

var _last3month=

DATE(

YEAR(_maxdate),MONTH(_maxdate)-3,DAY(_maxdate))

return

IF(

MAX('Table'[Date]) >= _last3month,1,0)``````

You can use the filters pane on the right to choose whether you want to see values from the last three months or all values from three months ago. The image below shows all the values from three months ago, to see the values from the last three months just change the 0's to 1's in the filter. If you want to calculate the value corresponding to the date, you can also use dax to filter 0 or 1 and then calculate.

Best Regards,

Clara Gong

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

Super User

Hi,

Does this pattern work?

Sales for 3 months ended select month = calculate([Total sales],datesbetween(calendar[date],edate(min(calendar[date]),-2),max(calendar[date])))

Sales in 3 months prior = calculate([Sales for 3 months ended select month],datesbetween(calendar[date],edate(min(calendar[date],-5),edate(min(calendar[date],-2))))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Last three month

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH))

Everything before three month

LTD(complete till date) =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),-3)
var _min = minx(all('Date'), 'Date'[Date]) // or minx(allselected('Date'), 'Date'[Date])
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Using window

Rolling 3 = CALCULATE([Net], WINDOW(-2,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Using window

Rolling before 3 = CALCULATE([Net], WINDOW(0,ABS, -3, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

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 - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors