Forecast by ytd distributed over the months

[Ilse_ScpDt]

Hi community,

I would like make a measure for the forecast by the ytd value and distributed this over the months so I can show it in the same graph as the YTD and Budget measures.

I would like to calculate the forecast like this:

YTD / number of last month * 12 

Example:

955/4*12 = 2865, this number needs to be distribted equaly by month

Another way to say it: every month adds another 955/4 = 238,75

year month	YTD	Forecast
2021 01	220	239
2021 02	460	478
2021 03	780	716
2021 04	955	955
2021 05	955	1194
2021 06	955	1433
2021 07	955	1671
2021 08	955	1910
2021 09	955	2149
2021 10	955	2388
2021 11	955	2626
2021 12	955	2865

How can I make this into a measure?

[v-luwang-msft]

Hi @Ilse_ScpDt ,

Could you pls share your pbix file? Remember to remove confident data.

Best Regards

Lucien

[v-luwang-msft · Accepted solution]

Hi @Ilse_ScpDt ，

You could try the following measure:

Forecast =
VAR test1 =
    MONTH ( NOW () ) - 1
VAR test2 =
    CALCULATE (
        VALUES ( 'Table'[YTD] ) / test1,
        FILTER ( ALL ( 'Table' ), 'Table'[month] = test1 )
    )
RETURN
    test2 * MAX ( 'Table'[month] )

Wish it is helpful for you!

Best Regards,

Lucien

[Ilse_ScpDt]

Hi, thanks for your reply. 

The measure looks very simple, but it isn't working for me. I don't have a table with YTD in it. I have this YTD as a measure and a seperate date table. 

How could I change the measure that it's working for me as well?

Kind regards.

[Jihwan_Kim]

Hi, @Ilse_ScpDt 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/lm26ng8aexuubdy/ilse.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@Ilse_ScpDt , I think I covered this under these two blogs

Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to...

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...