Define Filter and refer to previous filter

Anything related to PowerPivot and DAX Formuale
Lars
Posts: 1
Joined: Sun Sep 01, 2019 5:59 am

Define Filter and refer to previous filter

Postby Lars » Sun Sep 01, 2019 6:51 am

Hello,

this is the first time I'm using a forum at all...I know... So plaese let me know, in case I'm not following the standard rules ;)

Would need some support onto the following PowerPivot Issues, as I'm not able to find any solution even after screening plenty of hours in the web...

Intention: evaluate the capacity per location per quarter, by taken into consideration the overloaded capacity of the previous month.
- The raw data is being generated by merging multiple files and I have no option to change the layout and column set.
- The final reporting (in a pivot table out of PowerPivot) is also defaulted to the layout of the attachement (in reality it looks much nicer, this is an example only) :D

Issue: I need to calculate the Net Demand (=Demand Type A + Demand Type B) of each quarter. This Net Demand should also include (only) the overloaded portion of the previous quarter too,
in case the Net Demand of the previous quarter is higher than the Net Capacity (Capacity Type A + Capacity Type B) of the previous quarter.
In example:
- Location Asia
- Net Demand Quarter 2020Q1: 91 + 5 = 96
- Net Capacity Quarter 2020Q1: 69 + 22 = 91
--> Expected Lates: 91-96 = -5
In case of a negative value, this suppose to become an input criteria for the following month as "Lates carryover"
--> Net Demand Quarter 2020Q2: 59 + 6 + 5 (from the previus quarter)
Location Demand Type A Demand Type B Capacity Type A Capacity Type B Quarter
Asia 91 5 69 22 2020Q1
Asia 59 6 88 12 2020Q2
Asia 21 26 13 54 2020Q3
Asia 79 10 43 38 2020Q4
States 4 50 0 14 2020Q1
States 11 30 51 8 2020Q2
States 69 13 26 29 2020Q3
States 64 31 59 73 2020Q4
Europe 25 12 71 38 2020Q1
Europe 50 40 50 63 2020Q2
Europe 13 59 9 96 2020Q3
Europe 12 93 35 23 2020Q4

Somehow I was able to filter & limit the data on a specific quarter, but I was not able to include here also the delta of a previous month :?
=SUMX(FILTER(Tablename;Tablename[Quarter]= "Q1");Tablename[Net Capacity]-Tablename[Net Demand]) ----> Net Capacity & Demand are just combining the various input types
or I tried it with the following: =calculate(sum(tTablename[Delta Net Demand vs Net Capacity]);Tablename[Quarter]="Q1")
I did this for the various quarters, but I'm not able to combine all of them into 1 measure and I even cannot get the right link to include the package of Q1 to the calculation of Q2.

Sorry, this note was getting much longer than I originally thought and it is quite tough to get it explained in text only.
Therefore I have added an attachment for better explanation.
And not forget to mention, I'm a greenhorn in regards of powerpivot :|

Regards
Lars
Attachments
Load Evaluation.xlsx
(12.98 KiB) Downloaded 12 times

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 1 guest

cron