Pro rate monthly amounts over fiscal weeks

[watje255_ju]

Hello, 

I have a monthly forecast that is entered on the first/second day of the month, and I want to count the number of fiscal months in that month, pro rata the total amount, and then graph that pro rata'd amount over each fiscal week in that month so we can compare with weekly sales. 

Please see link to PBIX file Monthly Allocation Test.pbix and the desired result screenshot of the table below for the first few months 

Thanks!

[watje255_ju]

Hi Yilong Zhou, thanks so much for your reply. The picture of the table and graph i showed was the desired solution, using the dataset in the PBIX file/model  I sent a link to in the original post - the dataset looks like the below. Thanks!  Monthly Allocation Test.pbix 

Thanks! 

[Anonymous]

Hi @watje255_ju ,

I create a table as you mentioned.

Then I get an Index column and create a new calculated column.

Column =
VAR _Current = 'Table'[Fiscal Week2]
RETURN
    CALCULATE (
        MAX ( 'Table'[Monthly Allocation] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Fiscal Week2] = _Current )
    )

When I Put it into the Stacked column chart, you will get what you want.

Best Regards

Yilong Zhou

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