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.
Hello, I am trying to write a measure that calculates an average monthly value per team based on a month slicer.
Resource Situation Actual N+2 =
IF (
CALCULATE (
DISTINCTCOUNT ( 'Date'[Month] ),
ALLEXCEPT ( 'Date', 'Date'[Month] )
)
= CALCULATE ( DISTINCTCOUNT ( 'Date'[Month] ), ALL ( 'Date' ) ),
CALCULATE (
SUM ( 'Table'[Resource need %] ),
FILTER ( 'Table', 'Table'[Month Number] = MONTH ( TODAY ()) + 2 )
),
CALCULATE ( SUM ( 'Table'[Resource need %] ) , ALLEXCEPT('Table', 'Table'[Team name]),
FILTER ( 'Table','Table'[Month Number] = MAX('Table'[Month Number] + 2 ))
))
Hello, I am trying to write a measure that calculates an average monthly value per team based on a month slicer.
I have 2 tables in my model : a date table, and my main table consisting of the following columns : project name, departement, month (date of the start of the month), month number, and the share of FTE needed from this project and this month. We have dates covering several years in the source file.
The twist here is that I want an offset in this measure, showing the value for 2 months after the selected month.
There is an IF statement because if we don't select any month from the slicer, we want to display the value for the current month + 2. If we do select a month from the month slicer, I want the value for 2 months after this selection.
I have tried different version of this line
FILTER ( 'Table','Table'[Month Number] = MAX('Table'[Month Number] + 2 ))
By using DATEADD or EDATE, but none of this worked
Any idea on how to make this work ?
Thank you in advance
@LucieM , try a measure like
=
var _min =Minx('Date','Date'[Date])
var _max = eomonth(_min,2) //2 or -2 based on need
return
CALCULATE('Table'[Sales], Filter(Date,'Date'[Date] >=_min && 'Date'[Date] <=_max ))
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 |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |