Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 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
@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))
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |