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 Guys,
I have the folowing data in a table (ROWIND) :
The Date is linked to a calendar table which goes from 01/01/2018 to 31/12/2021.
I would like create a measure to get this visual :
It means for each period (Here it's years but it could be quarters or months)
=> to have the sum of the maxdate of the period of the visual (dimdate).
In my example : Max date 2018 = 31/12/2018 | Max date 2019 is = 31/12/2019 etc. and get the related sum value of these max date of the table ROWIND.
And this is not all because as you see in my example for 2021, I have no date value in ROWIND in 2021. So in this case I would like to have the sum of the MaxDate of ROWIND (here maxdate = 31/05/20 and sum = 990)
this is the value results I would like :
Hope my explanation are clear ?
Thank you for your help ! 🙂
Solved! Go to Solution.
Try:
Assumin you have a measure for the sum of ROWIND[TOTEXPRR]
Sum on max date =
VAR _LastDate =
CALCULATE (
MAX ( Calendar[Date] ),
FILTER ( Calendar, NOT ( ISBLANK ( [Sum TotExprr] ) ) )
)
RETURN
CALCULATE ( [Sum TotExprr], FILTER ( Calendar, Calendar[Date] = _LastDate ) )
Proud to be a Super User!
Paul on Linkedin.
Hi @Lejouquard ,
Here are the steps you can follow:
1. Create calculated table.
Date = CALENDAR(DATE(2018,1,1),DATE(2021,12,31))
2. Create measure.
Measure =
var _year=YEAR(MAX('Date'[Date]))
return
SWITCH(
_year,
2018,0,
2019,SUMX(FILTER(ALL('ROWIND'),[date]=MAXX(FILTER(ALL(ROWIND),YEAR([date])=2019),[date])),[Totexprr]),
2020,SUMX(FILTER(ALL('ROWIND'),[date]=MAXX(FILTER(ALL(ROWIND),YEAR([date])=2020),[date])),[Totexprr]),
2021,SUMX(FILTER(ALL('ROWIND'),[date]=MAXX(ALL(ROWIND),[date])),[Totexprr]),
BLANK())
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try:
Assumin you have a measure for the sum of ROWIND[TOTEXPRR]
Sum on max date =
VAR _LastDate =
CALCULATE (
MAX ( Calendar[Date] ),
FILTER ( Calendar, NOT ( ISBLANK ( [Sum TotExprr] ) ) )
)
RETURN
CALCULATE ( [Sum TotExprr], FILTER ( Calendar, Calendar[Date] = _LastDate ) )
Proud to be a Super User!
Paul on Linkedin.
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |