topic Re: Pro rate monthly amounts over fiscal weeks in DAX Commands and Tips

Pro rate monthly amounts over fiscal weeks

watje255_ju — Wed, 17 Apr 2024 00:09:28 GMT

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!

Re: Pro rate monthly amounts over fiscal weeks

Anonymous — Wed, 17 Apr 2024 02:27:12 GMT

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.

Re: Pro rate monthly amounts over fiscal weeks

watje255_ju — Wed, 17 Apr 2024 02:39:10 GMT

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!