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.
I am calculating a cumulative value at a cut-off date, but records that have already expired do not appear on subsequent cut-off dates. I would like to know how I can filter that data that ended in past months.
Below is an image of what I want to calculate
It's like a sum of the last record
Hi @fjcampos,
Please check following steps as below and see if the result achieve your expectation:
1. Create calculated table as silcer:
Table 2 = DISTINCT('Table'[Date])
2. Create measures:
Measure =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALL ( 'Table' ),
'Table'[Name] = MAX ( 'Table'[Name] )
&& 'Table'[Date] <= SELECTEDVALUE ( 'Table 2'[Date] )
)
)
Measure 2 =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = [Measure] )
)
3. Result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your answer, but try to replicate your measure but it doesn't work.
I attach an example file of how is my model Pbix
Hi @fjcampos ,
Couldn't achieve your Pbix, any sample data would be helpful.
Thanks,
Jay
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |