Recursive Calculation current Month different

[Imex197]

Hello,

I need your help.

I want to calculate YTD "Won" Volume + expected Volume for next months until 12/20. But the current month should include Won Volume and the expected Volume.

My Dax Code for this measure is:

WonYTD and Expected CY = IF (

NOT ( ISBLANK ( [Estimated Opportunity Value ACD won] ) );

[Estimated Opportunity Value ACD won];

CALCULATE (

[Expected Prob Value ECD])

)

As visual I use the Waterfall:

Thanks in advance.

Cheers

Imex197

[amitchandak]

Not Sure I got it completely. Can you share sample data and sample output?

You can use time intelligence for YTD with date calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Imex197]

Hi amitchandak,

thanks for your fast reply. I Can´t share any data.

let me try again:

[Mariusz]

Hi @Imex197 

Try something like below

Measure = 
VAR __dates = DATESYTD( 'Calendar'[Date] )
VAR __endOfMonth =  
CALCULATE(
    MAX( 'Calendar'[Date] ),
    FORMAT( 'Calendar'[Date], "YYYYMM" ) = FORMAT( TODAY(), "YYYYMM" )
)
VAR __before = CALCULATE(
    SUM( 'Table'[Value] ),
    FILTER( __dates, 'Calendar'[Date] <= __endOfMonth )
)
VAR __after = CALCULATE(
    SUM( 'Table'[Value] ),
    FILTER( __dates, 'Calendar'[Date] > __endOfMonth )
)
RETURN 
__before + __after

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Imex197]

Hi @Mariusz 

thanks for your help.

I changed the Format to "YYYYMMDD" now it suits better 🙂

But I wanted to have isolated values.

Is there a chance to have these value isolated per month and not cumulative?

Cheers

Imex197

[Anonymous · Accepted solution]

Hi @Imex197 

I have tried to make the below solution which I believe works just fine. I assume you have a calendar table which you should also use on the axis.

Measure = 
IF(
    -- VALUES(Calendar[Date]) returns all seletected days in a given context
    TODAY() in VALUES( 'Calendar'[Date]);
    SUM( 'Fact'[Expected]) + SUM( 'Fact'[Won]); -- current month
    IF(
        -- If today is larger than the largest date in the filter context then we are looking at previous months thus use [Won] else [Expected]
        TODAY() > MAXX( VALUES( 'Calendar'[Date]); [Date]); 
        SUM( 'Fact'[Won]);
        SUM( 'Fact'[Expected])
    )
)

If this works then please mark it as the accepted solution. Kudos is also appreciated.

BR

[Imex197]

Hi @Anonymous ,

this is perfect! Thank you!