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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

M
  • Question
Power BI DAX Rollover Function In Matrix Needs to Factor for Negatives
Replies
3
Views
156
marksaba20
M
A
  • Solved
Count Number of Repeat Customers
Replies
7
Views
381
JGordon11
JGordon11
T
  • Question
slow measure needs help!
Replies
0
Views
543
tonysharpe
T
D
  • Question
DAX measure calculate sum of current and one previous month
Replies
0
Views
576
dicken
D
O
  • Question
Simple sum measure with multiple filters
Replies
0
Views
345
Orfevre
O

Forum statistics

Threads
1,214,415
Messages
6,119,382
Members
448,889
Latest member
TS_711

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