minx - one month

[jameshoneywill]

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

[amitchandak · Accepted solution]

@jameshoneywill ,

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

[v-kaiyue-msft]

Hi @jameshoneywill ,

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.

If your Current Period does not refer to this, please clarify in a follow-up reply.

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.

[Ashish_Mathur · Accepted solution]

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))))

[amitchandak · Accepted solution]

@jameshoneywill ,

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