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 All,
I have created below measures:
Month Year | Project Profit | YTD Project Profit | Profit Intermediate | Profit | Loss Intermediate | Loss | YTD Profit | YTD Loss |
Jan-19 | 120 | 120 | 120 | 150 | 0 | -30 | 150 | -30 |
Feb-19 | 240 | 360 | 360 | 420 | 0 | -60 | 570 | -90 |
Mar-19 | 360 | 720 | 720 | 900 | 0 | -180 | 1050 | -210 |
Apr-19 | 2150 | 2870 | 2870 | 2890 | 0 | -20 | 3040 | -50 |
May-19 | 3154 | 6024 | 6024 | 6048 | 0 | -24 | 6198 | -54 |
Jun-19 | 152465 | 158489 | 158489 | 159000 | 0 | -511 | 159150 | -541 |
Jul-19 | 52656 | 211145 | 211145 | 245788 | 0 | -34643 | 245938 | -34673 |
Aug-19 | 566 | 211711 | 211711 | 256989 | 0 | -45278 | 257139 | -45308 |
Sep-19 | 56569 | 268280 | 268280 | 300045 | 0 | -31765 | 300195 | -31795 |
@Anonymous ,
If I understood the issue, try this:
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
YTD Loss Calculated = VAR _currentMY = MAX ( Project[Month Year] ) VAR _Runtotal = CALCULATE ( SUM ( Project[Loss] ), FILTER ( ALL ( Project ), Project[Month Year] <= _currentMY ) ) RETURN _Runtotal ================================= YTD Profit Calculated = VAR _currentMY = MAX ( Project[Month Year] ) VAR _Runtotal = CALCULATE ( SUM ( Project[Profit] ), FILTER ( ALL ( Project ), Project[Month Year] <= _currentMY ) ) RETURN _Runtotal
Proud to be a Super User!
@Nathaniel_C wrote:@Anonymous ,
If I understood the issue, try this:
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
YTD Loss Calculated = VAR _currentMY = MAX ( Project[Month Year] ) VAR _Runtotal = CALCULATE ( SUM ( Project[Loss] ), FILTER ( ALL ( Project ), Project[Month Year] <= _currentMY ) ) RETURN _Runtotal ================================= YTD Profit Calculated = VAR _currentMY = MAX ( Project[Month Year] ) VAR _Runtotal = CALCULATE ( SUM ( Project[Profit] ), FILTER ( ALL ( Project ), Project[Month Year] <= _currentMY ) ) RETURN _Runtotal
I was trying to use your YTD Profile formula for one of my same scenarios but my date range is from 2012 to 2019 and it is giving me YTD from 2012 to 2019. How to limit it for the current year?
Hi @Anonymous ,
Try changing the ALL() to ALLEXCEPT(tablename, tablename[Year column] or if you really only wanted one year like 2019, then you could add a var where = table[year column] = 2019 and then use that to filter your rows.
FILTER ( ALL ( Project, ), Project[Month Year] <= _currentMY )
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C , sorry but it didn't worked. Tried your formula but was not getting expected result. Business don't need that calculation for now. All are measures and i want YTD of measure which is already YTD. Let me know if you need more information or in case of any queries.
@Anonymous , @Anonymous ,
Here is my pbix Running Total P&L
This may help you understand the code a little better.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
@Nathaniel_C I checked your pbix but my issue is i want YTD of
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |