cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lbad1601 Regular Visitor
Regular Visitor

YTD of YTD measure

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 YearProject ProfitYTD Project ProfitProfit IntermediateProfitLoss IntermediateLossYTD ProfitYTD Loss
Jan-191201201201500-30150-30
Feb-192403603604200-60570-90
Mar-193607207209000-1801050-210
Apr-1921502870287028900-203040-50
May-1931546024602460480-246198-54
Jun-191524651584891584891590000-511159150-541
Jul-19526562111452111452457880-34643245938-34673
Aug-195662117112117112569890-45278257139-45308
Sep-19565692682802682803000450-31765300195-31795
 
I tried all sorts of measure using TOTALYTD, quick measures but nothing is helping.
6 REPLIES 6
Nathaniel_C Super Contributor
Super Contributor

Re: YTD of YTD measure

@lbad1601 ,

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

 

ytd profit.PNG

 

lbad1601 Regular Visitor
Regular Visitor

Re: YTD of YTD measure

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

mohammedfareed2 Frequent Visitor
Frequent Visitor

Re: YTD of YTD measure


@Nathaniel_C wrote:

@lbad1601 ,

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

 

ytd profit.PNG

 



@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 Super Contributor
Super Contributor

Re: YTD of YTD measure

Hi @mohammedfareed2 ,

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

Nathaniel_C Super Contributor
Super Contributor

Re: YTD of YTD measure

@lbad1601 , @mohammedfareed2 ,

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

 

lbad1601 Regular Visitor
Regular Visitor

Re: YTD of YTD measure

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 287 members 3,427 guests
Please welcome our newest community members: