Forum Discussion

jameshoneywill's avatar
jameshoneywill
Frequent Visitor
2 years ago
Solved

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

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.