Help with my measure Previous month

[carlobonan]

Hi everyone, I am trying to modify this measurement without success.

I would like to calculate the maximum date of the previous month compared to the last weekly update.

For example:

(the data is updated every Thursday)

this week i have like lastdate
12/03/2021 and I would like it to automatically take the maximum date of the previous month 26/02/2021.

With the next data updates I will have this situation

02/26/2021
05/03/2021
12/03/2021
19/03/2021
03/26/2021
02/04/2021
09/04/2021
04/16/2021

so for example when I have the update with date 16/04 I want it to automatically calculate the maximum date of the previous month (26/3)

my measure =
var current_month= eomonth(today(),-2)
return CALCULATE(SUM('01_DB_GLOBALE'[TOT_COLLOC]), FILTER('01_DB_GLOBALE', '01_DB_GLOBALE'[TIPO_PTF]="a" || '01_DB_GLOBALE'[TIPO_PTF]="b"), eomonth('01_DB_GLOBALE'[DATA_CORRETTA],0)=current_month )

thanks

[carlobonan]

@amitchandak  the first condition "var current_month= eomonth(today(),-2)" 

I think it is not good because it calculates the previous month, but I would like this to be calculated based on the maximum date of my table, but I cannot understand how to write the measure correctly

[amitchandak · Accepted solution]

@carlobonan , check if these two can help

Last month of Data complete =
var _max1 = maxx(allselected('Order'), 'Order'[Date])
var _max = eomonth(_max,0)
var _min = eomonth(_max,-1)+1

return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max))

2nd Last month of Data complete =
var _max1 = maxx(allselected('Order'), 'Order'[Date])
var _max = eomonth(maxx(filter(all('Order'),'Order'[Date] <= eomonth(_max,-1)),'Order'[Date]),0)
var _min = eomonth(_max,-1)+1

return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max))

[amitchandak]

@carlobonan , Not very clear.

You need to do this with help from a date table

example

MTD Today =
var _min = eomonth(today(),-1)+1
var _day = datediff(_min, today(),day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max && 'Date'[Day of Year] <= _day) )

Previous Month complete =
var _min = eomonth(today(),-2)+1
var _max = eomonth(today(),-1)
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.