NEED HELP WITH DAX FORMULA ( YTD ACTUAL + REMAINING BUDGET VS YTD BUDGET)

[Anonymous]

Hi all,

I wanted to create something using power BI which I have 2 sets of data comprise ACT and BUD within the same year.

Where I have my actual revenue for 3 months and 12 months of budget. But when I select my slicer accordingly to my months I need a projection something like below shown.

As I only have 3 months of actual, I need to compare 6 months of budget using 3 months actual and 3 month budget depend on the slicer chosen by the user (whether is up to 12 month or lesser). 

The structure of my data look like this: **I will unpivot ACT and BUD

Ops Unit	ID	MTH	MTH ORDER	ACT	BUD
A	REVENUE	JUL	1	10	20
A	REVENUE	AUG	2	10	20
A	REVENUE	SEP	3	10	20
A	REVENUE	OCT	4		20
A	REVENUE	NOV	5		20
A	REVENUE	DEC	6		20
A	REVENUE	JAN	7		20
A	REVENUE	FEB	8		20
A	REVENUE	MAR	9		20
A	REVENUE	APR	10		20
A	REVENUE	MAY	11		20
A	REVENUE	JUN	12		20
B	REVENUE	JUL	1	100	50
B	REVENUE	AUG	2	100	50
B	REVENUE	SEP	3	100	50
B	REVENUE	OCT	4		50
B	REVENUE	NOV	5		50
B	REVENUE	DEC	6		50
B	REVENUE	JAN	7		50
B	REVENUE	FEB	8		50
B	REVENUE	MAR	9		50
B	REVENUE	APR	10		50
B	REVENUE	MAY	11		50
B	REVENUE	JUN	12		50

Eg: When I select Dec then it give me something like this

Ops Unit	FCT (3month actual + 3 month budget)	BUD
A	90	120
B	450	300

I tried a few method but it seem unable to create what I wanted and also search within forum too. 

Is there any way I can do? Please help. Thank you.

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

If your table doesn’t have a date column, just have a month column, we can create a MTH table and two measures to meet your requirement.

Please pay attention, don’t create a relationship.

1. Create a table that contains MTH and MTH order.

slicer = SUMMARIZE('Table','Table'[MTH],'Table'[MTH ORDER])

2. Then we can use it to create a slicer.

3. We can create two measures.

BUD measure = 
VAR _Select =
    SELECTEDVALUE ( slicer[MTH ORDER], 1 )
RETURN
    CALCULATE (
        SUM ( 'Table'[BUD] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
                && 'Table'[MTH ORDER] <= _Select
        )
    )

ACT measure = 
VAR _Select =
    SELECTEDVALUE ( slicer[MTH ORDER], 1 )
VAR _ACT =
    CALCULATE (
        SUM ( 'Table'[ACT] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
                && 'Table'[MTH ORDER] <= _Select
        )
    )
VAR _max_act_month =
    CALCULATE (
        MAX ( 'Table'[MTH ORDER] ),
        FILTER ( 'Table', 'Table'[ACT] = MAX ( 'Table'[ACT] ) )
    )
VAR _BUD_max_act_month =
    CALCULATE (
        SUM ( 'Table'[BUD] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
                && 'Table'[MTH ORDER] <= _max_act_month
        )
    )
VAR _BUD =
    CALCULATE (
        SUM ( 'Table'[BUD] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Ops Unit] = MAX ( 'Table'[Ops Unit] )
                && 'Table'[MTH ORDER] <= _Select
        )
    )
RETURN
    IF ( _Select > _max_act_month, _BUD_max_act_month + _ACT, _ACT + _BUD )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Anonymous]

Hi Zhenbw,

WOW. It seems like what I needed for my dashboard.

Let me try and will get back to you see asap as I just return back to work.

[Anonymous]

Hello, @v-zhenbw-msft 

Sorry can trouble you to share you pbix file as I it written it incompatible with my current version of Power BI desktop.

Even after redownload it, it seems that still appear this issue.

Please advise? Thanks.

[v-zhenbw-msft]

Hi @Anonymous ,

Please try the Power BI Store version.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , In case you do not have date

create one like

date = Date(Table[Year], table[MTH], 1) //Numeric month

then use date table and time intelligence

YTD Act= CALCULATE(SUM(Table[ACT]),DATESYTD('Date'[Date],"12/31"))

YTD Bud= CALCULATE(SUM(Table[BUD]),DATESYTD('Date'[Date],"12/31"))

Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

In case you do not have month as a number - refer first few mins of this video

https://www.youtube.com/watch?v=yPQ9UV37LOU&t=30s

[Anonymous]

Hi Amit, 

Thanks for your YTD DAX formula but it does not solved my issue having both actual and budget together.

Anyway thanks for your help.