Find Anniversary

DAX DAX Calculations
1

Hi

I’m obtaining data from an SQL view where i find customers who have joined via a specific avenue which we pay for. In the Power BI report the user can select the join date months from a filter. Once the month range is selected Power BI will return all those with a join date in that time range with the fees we have to pay. All of this works fine

My issue is we have to pay for anniversaries too, so if someone joined in October 2020, in October 2021 I will have to pay an anniversary fee (this fee is calculated in the view, but when to pay it is not). Can I recognise these with Dax?

current table similar to:
Name - Join Date - Join Fee - Minus Cancellation Fee - Anniversary Fee - Total Fee
John Doe - 1/10/2021 - 200 - 0 - 0 - 200
Jane Doe - 1/10/2020 - 0 - 0 - 50 - 50

If someone chooses October to obtain the joiners, I want it to also include those with an anniversary (every anniversary) in October as well. Whenever the month is the same as the join date month it returns the anniversary fee (from year 1 onwards), but not the joining fee.

I can’t send the pbix or screenshots as it is real customers

2

Hi,

Can you provide the dummy data with expected result because above explanation is not very help full to solve your problem.

Thanks,
Anurag

3

@chris.wilson ,

Welcome to the forum - great to have you here!

This is a really interesting problem, and a bit trickier than I originally thought it would be. I created a sample dataset for testing using the eDNA Data Randomizer tool, and have a solution that is 80% of the way there. Just one final issue to debug.

Have some things I need to take care of this weekend, but should have a full solution to you tomorrow.

– Brian

4

Thanks,

I’ve created a summarised version with clean data that I’ve uploaded (see table page). My idea is that the Annual Fee column will only have the original value if the date selected from the filter includes a month that their anniversary will fall on, unless it’s their join year, or they have cancelled, or their term date has passed. Otherwise it will be 0
Agency - cleaned.pbix (404.6 KB)

5

@chris.wilson ,

Thanks for providing the PBIX. That’s a bit of a different construct than I’d imagined, but totally doable. Should have something back to you in a bit…

EDIT: I’m going to need to make some changes to your model. Can you please also provide the agent joins.xlsx’ file?

Thanks!

  • Brian
6

agent joins.xlsx (19.9 KB)
Attached is the file you requested

1 Like
7

@chris.wilson ,

Okay, I think I’ve got a solution for you, but you’ll need to double-check the logic just to make sure it reflects the conditions that you’re looking for. Here are the three key measures:

Cond Join Fee =

VAR SelMoY = LOOKUPVALUE( Dates[Month & Year], Dates[Date], SELECTEDVALUE( Data[Health Join Entry Date] ))

VAR Result =

IF( SelMoY IN VALUES( 'Disconn Dates'[Month & Year] ), [Total Join Fee], BLANK())

RETURN Result

Cond Cancel Fee =

VAR TermMoY = LOOKUPVALUE( Dates[Month & Year], Dates[Date], SELECTEDVALUE( Data[Term Date] ))

VAR Result =

IF( TermMoY IN ALL( 'Disconn Dates'[Month & Year] ), [Total Cancel Fee], BLANK())

RETURN

IF( ISBLANK( SELECTEDVALUE( Data[Term Date] )) , BLANK(), Result )

Cond Monthly Fee =

VAR IsJoinYr =

IF( ISBLANK( [Cond Join Fee] ), 0, 1 )

VAR IsTerminated =

IF( ISBLANK( [Cond Cancel Fee] ), 0, 1 )

VAR SameMonth =

IF( MONTH( SELECTEDVALUE( Data[Join Entry Month] )) IN VALUES( 'Disconn Dates'[MonthOfYear] ), 1, 0 )

VAR AllCond =

IsJoinYr + IsTerminated + (1 - SameMonth)

VAR Result =

IF( AllCond = 0, [Total Annual Fee], BLANK() )

RETURN Result

And here’s what it looks like I’ll put together:

Snag_16f51728
Snag_16f517281025×352 29.3 KB

I hope this is helpful. Full solution file attached below.

– Brian
eDNA Forum - Agency - cleaned solution.pbix (493.5 KB)

1 Like
8

Thanks for this. Unfortunately it didn’t work for me, but the solution I created was as follows:

In SQL create a field with the month name of the join, e.g. January
Create another for Termination month name
and another for Annual (which was the same as join month name!)

In transform Data
Then I uploaded the query twice. The first one for joins and terms and ensured months since join was < = 12 (to remove annual fees)
For Annual Fees, I uploaded the same query, but made the months since join field >12 (to remove joins and terms)

I then limited the date table to the 1st of each month only.

Model
In the model I connected the date table month name to the join month name and inactive join to termination month name.

I then created a link from Date table to the annual fees table for month name to join month name.

In Report Section
I made 3 tables in Power BI, one for each outcome, all filtered by the Month name field. This worked for me

I hope my description is understandable and clear…

1 Like