[DeepakParticipant Started: 2Replies: 9Total: 11]

Hi Tom,

Was able to crack the same by following formula:

=
“Week ”
& INT (
(
‘Calendar'[Date]
– WEEKDAY ( ‘Calendar'[Date] – 1 )
– DATE ( YEAR ( ‘Calendar'[Date] ) – ( MONTH ( ‘Calendar'[Date] ) < 4 ), 4, 1 )
)
/ 7
)
+ 2

The week number starts from 1st of April according to Fiscal Year definition of India.

-Deepak

[DeepakParticipant Started: 2Replies: 9Total: 11]

Moreover, while preparing a budget, it has to go through several iterations meaning the budgeted quantity will keep changing and that’s precisely the reason I want to make the calculations dynamic which will take care of automatic allocation at last year’s proportions.

[DeepakParticipant Started: 2Replies: 9Total: 11]

Thanks for the inputs, Tom. I’m able to pull all the data in the Power Pivot and able to successfully do the allocation thing using Calculated columns, however, my basic issue is how to do the same either using Power Query or using Calculated Field/Measure because as per all the text that I’ve read so far recommends not to use Calculated Columns when more efficient calculated measures are available.

[DeepakParticipant Started: 2Replies: 9Total: 11]

Sorry for the late reply as it was late here in India. Yes, the source of the data is a relational database. Would be great if you can help me in this regard as Excel gives up on these many rows.

[DeepakParticipant Started: 2Replies: 9Total: 11]

Dear Tom

I can very well see the number of rows increasing to millions in my business context as I used dummy data only in the sample workbook here. And I see a very big problem with SUMIFS function working on those millions of rows to arrive at those proportions for allocation. With 2 lac rows my system powered by i7 takes close to 7 minutes to calculate the same and you can very well imagine the same with 1 million rows.

In this context, if you can give me some hint on how to execute this proportion business using Power Query, my life would be far more simpler. 😀

Thanks a lot for the support. 🙂

[DeepakParticipant Started: 2Replies: 9Total: 11]

And, thanks for the workbook. I’ve downloaded the same.

[DeepakParticipant Started: 2Replies: 9Total: 11]

Thanks a ton, Tom. I really appreciate the effort you have put in to explain it in such an awesome manner. I’ll be working on enhancing my skills continuously and surely, I’m going to buy the recommended book for Power Query.

You made my day. Thanks again.

[DeepakParticipant Started: 2Replies: 9Total: 11]

Thanks a lot, Tom. As I’m new to DAX and Power Query, could you please help me with my workbook on how to do the same with power query?

Attachments:

You must be logged in to view attached files.

[DeepakParticipant Started: 2Replies: 9Total: 11]

Actually, if you observe closely, the “BgtInputSheet” is at the product category level and the main issue is how will a calculated field will handle the same at different granularity levels while plotting the pivot and using the same. I tried attempting the same using the article on Budget Patterns on sqlbi.com but unfortunately I was not successful and hence this post.

Would be great if you can help me on this. Thanks.

[Author]

Posts