YTD of YTD measure

[Anonymous]

Hi All,

I have created below measures:

Project Profit = SUMX('Loss Factor', ('Loss Factor'[Gross Revenue] - ( 'Loss Factor'[Direct Expenses] + 'Loss Factor'[Staff Costs] + 'Loss Factor'[OHonSubConsultant] ))*[Budget Rate])

YTD Project Profit = TOTALYTD(ROUND([Project Profit],2), 'Calendar'[Date], ALL('Calendar'))

Profit Intermediate =

IF([YTD Project Profit] > 0, [YTD Project Profit], 0)

Profit =

IF(HASONEVALUE('Project'[Project]), [Profit Intermediate],TOTALYTD(SUMX(VALUES('Project'[Project]), [Profit Intermediate]),'Calendar'[Date]))

Loss Intermediate =

IF([YTD Project Profit] < 0, [YTD Project Profit], 0)

Loss =

IF(HASONEVALUE('Project'[Project]), [Loss Intermediate],TOTALYTD(SUMX(VALUES('Project'[Project]), [Loss Intermediate]),'Calendar'[Date]))

Now i want 2 new measures YTD Profit and YTD Loss as shown below:

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

I tried all sorts of measure using TOTALYTD, quick measures but nothing is helping.

[Nathaniel_C]

@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

[Anonymous]

---

@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

---

@Nathaniel_C

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? 

[Nathaniel_C]

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

[Anonymous]

@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.

[Nathaniel_C]

@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

[Anonymous]

@Nathaniel_C I checked your pbix but my issue is i want YTD of 

[YTD Profit Calculated] and i used below formula to achieve same but it didn't worked.

YTD of YTD Profit Calc =

VAR _currentMY =

MAX ( Project[Month Year] )

VAR _Runtotal =

CALCULATE (

[YTD Profit Calculated],

FILTER ( ALL ( Project ), Project[Month Year] <= _currentMY )

)

RETURN

_Runtotal

Would really appreciate if you can help me.