Dax - filter, all, allexcept

D

DickyMoo

New Member
Joined
Mar 8, 2016
Messages
32
Hi,

I have a monthly snapshot of our member data, and am trying to create a measure showing the income effects of members joining / leaving / dying etc.

For example, members who have died in the month are identified by a flag column, Died_Flag. I would like to calculate the income for this group of members from the previous month. I currently have this, but it is not working:

Code: 
Variance Monthly Fee - Died = 
VAR tblmembers =
    FILTER ( Fee_Analysis, Fee_Analysis[Died_Flag] = 1 )
RETURN
    CALCULATE ( [Monthly Fee - Prior Month], tblmembers )

Does anyone have any suggestions on how to go about this? The steps I need to do are as follows:

- Identify members who died in the current month by filtering the flag column
- Calculate the Monthly Fee for these members for the previous month

I think my problem is that the tblmembers also has a month filter on it, but I'm not sure how to sort that out.

Thanks for any assistance.

Richard
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Could you post a sample of your tables? I am guessing you have a Fee_Analysis fact table plus a Calendar table?
 
Upvote 0
Here you go, hopefully this is clear. Let me know if otherwise. The Calendar table is pretty standard, one day per row.

Member 2 died in August so, in August, I would like to see that he had a fee of £25 in the prior month.
Member 4 resigned in August so, in August, I would like to see that he had a fee of £40 in the prior month.

Code: 
Fee_analysis table:[TABLE="width: 550"]
<tbody>[TR]
[TD]Member ID[/TD]
[TD]Date[/TD]
[TD]Fee[/TD]
[TD]Died_Flag[/TD]
[TD]Resigned_Flag[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-Jul[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]01-Jul[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]01-Jul[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]01-Jul[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-Aug[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]01-Aug[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]01-Aug[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]01-Aug[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Desired output:
[TABLE="width: 330"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Income Variance Due to Died[/TD]
[TD]Income Variance Due to Resigned[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD="align: right"]-25[/TD]
[TD="align: right"]-40[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Does this do what you are after?

Income Variance Due to Died :=<br><span class="Keyword" style="color:#0070FF">SUMX</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#969696"> (</span> Dates[MonthName] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Fee_Analysis[Fee] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">CALCULATETABLE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#969696"> (</span> Fee_Analysis[Member ID] <span class="Parenthesis" style="color:#969696">)</span>,<br>            Fee_Analysis[Died_Flag] = <span class="Number" style="color:#EE7F18">1</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">DATEADD</span><span class="Parenthesis" style="color:#969696"> (</span><br>            Dates[Date],<br><span class="indent8">        </span><span class="indent4">    </span><span class="Number" style="color:#EE7F18">-1</span>,<br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">MONTH</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>

Income Variance Due to Resigned :=<br><span class="Keyword" style="color:#0070FF">SUMX</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#969696"> (</span> Dates[MonthName] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">CALCULATE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">SUM</span><span class="Parenthesis" style="color:#969696"> (</span> Fee_Analysis[Fee] <span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">CALCULATETABLE</span><span class="Parenthesis" style="color:#969696"> (</span><br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">VALUES</span><span class="Parenthesis" style="color:#969696"> (</span> Fee_Analysis[Member ID] <span class="Parenthesis" style="color:#969696">)</span>,<br>            Fee_Analysis[Resigned_Flag] = <span class="Number" style="color:#EE7F18">1</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span>,<br><span class="indent8">        </span><span class="Keyword" style="color:#0070FF">DATEADD</span><span class="Parenthesis" style="color:#969696"> (</span><br>            Dates[Date],<br><span class="indent8">        </span><span class="indent4">    </span><span class="Number" style="color:#EE7F18">-1</span>,<br><span class="indent8">        </span><span class="indent4">    </span><span class="Keyword" style="color:#0070FF">MONTH</span><br><span class="indent8">        </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="indent4">    </span><span class="Parenthesis" style="color:#969696">)</span><br><span class="Parenthesis" style="color:#969696">)</span><br>
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
DAX PIVOT: Projects Closure Date Field - Number of Days Change - between differing Forecast Versions
Replies
0
Views
395
phil134
P
M
  • Question Question
Power BI DAX Rollover Function In Matrix Needs to Factor for Negatives
Replies
3
Views
347
marksaba20
M
K
  • Solved
DAX median in pivot table for an "almost empty" column
Replies
1
Views
211
Katchi
K
A
  • Solved
Count Number of Repeat Customers
Replies
7
Views
653
JGordon11
JGordon11
T
  • Question Question
slow measure needs help!
Replies
0
Views
692
tonysharpe
T

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top