Custom calendar time intelligence  [Solved]

Anything related to PowerPivot and DAX Formuale
andredl
Posts: 47
Joined: Wed Sep 23, 2015 9:56 pm

Custom calendar time intelligence

Postby andredl » Wed May 17, 2017 10:10 pm

I have a budget by month (Budget Qty), and I wish to calculate a Cover Qty that is the sum of the Budget Qty in the current month plus the budget of X-1 following months, where X is determined by the Month Name (defined in a separate table).

For example, if the Month is January, I want to have 2 months' cover (defined by the separate table), therefore the Budget Qty of January plus the Budget Qty of February.

Similarly, if the month is October, I want 4 months' cover extending 4-1=3 months into the future (Oct, Nov, Dec, Jan).

I have written a formula to achieve this, however it is not returning the desired result:
=CALCULATE (
[Budget Qty],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[YMIndex] >= MAX ( 'Calendar'[YMIndex] )
&& 'Calendar'[YMIndex]
<= MAX ( 'Calendar'[YMIndex] ) + [Cover]
- 1
)
)

Any thoughts on where I'm going wrong would be appreciated.
Attachments
Cover example.xlsx
(274 KiB) Downloaded 19 times

jiny680
Posts: 2
Joined: Thu Oct 27, 2016 7:10 pm

Re: Custom calendar time intelligence  [Solved]

Postby jiny680 » Thu May 18, 2017 2:48 pm

Using calculate column
Attachments
Cover example.xlsx
(295.92 KiB) Downloaded 23 times

andredl
Posts: 47
Joined: Wed Sep 23, 2015 9:56 pm

Re: Custom calendar time intelligence

Postby andredl » Thu May 18, 2017 7:54 pm

Thanks a lot.. Though I'm still not sure why my original formula didn't return the correct result. Is that something you can comment on?


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest