Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Our company rents musical instruments to school children. There are two types of rentals - beginning rental instruments, and advancing rental instruments. There are two types of beginning rentals - we call them Starter Packs and Intro Offers. The data in the sample of our table below should help us break these down.
Table name - 'Rental Accounts - scrental'
Contract Date | 1st Pmt Due Date | Down Payment |
8/12/2013 | 10/12/2013 | 17.12 |
9/1/2016 | 10/1/2016 | 0 |
7/2/2014 | 9/2/2014 | 17.16 |
8/29/2015 | 9/29/2015 | 0 |
12/2/2013 | 1/2/2014 | 178.04 |
If there is no down payment, it is a Beginner Rental - Starter Pack. Above, there are 2.
If there is a down payment with a 2 month interval between the Contract Date and 1st Pmt Due Date, it is a Beginner Rental - Intro Offer. Above, there are 2.
If there is a down payment and a 1 month interval between the Contract Date and 1st Pmt Due Date, it is an Advancing Rental. Above, there is 1.
What formula(s) should I use to make this happen? I want to be able to ask these questions:
Thanks for your help!
Solved! Go to Solution.
Hi @kincaids,
The DAX provided by @alanhodgson is to return one column in report instead of Query Editor. You can create a calculated column in report like this:
In your scenario, as you want to return the count value of Beginner Rentals, Advancing rentals, Starter Packs, Intro Offers, I would suggest you create four measures then place each measure in separate card visual.
Beginner Rentals = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental',DATEDIFF('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[1st Pmt Due Date],MONTH)=2),'Rental Accounts - scrental'[Down Payment]<>0) + CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental','Rental Accounts - scrental'[Down Payment]=0))
Advancing Rentals = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental',DATEDIFF('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[1st Pmt Due Date],MONTH)=1),'Rental Accounts - scrental'[Down Payment]<>0)
Starter Packs = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental','Rental Accounts - scrental'[Down Payment]=0))
Intro Offer = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental',DATEDIFF('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[1st Pmt Due Date],MONTH)=2),'Rental Accounts - scrental'[Down Payment]<>0)
Best Regards,
Qiuyun Yu
I think it would be easier to use the Query editor to do this rather than a measure.
You could add a custom column and use M's DateTime functions to calculate the number of months between the two dates.
I use something like this when i do that type of calc ( There may be a better way but im not a Pro with power query)
"MonthsBetween=Date.Month([1st Payment Due Date])-Date.Month([Contract Date])+((Date.Year([1st Payment Due Date])-Date.Year([Contract Date]))*12)"
Then add a conditional column that checks your Criteria to output the correct type.
IF Downpayment = 0 : Beginner Rental - Starter Pack
ELSE IF MonthsBetween = 1 : Advancing Rental
ELSE IF MonthsBetween = 2: Beginner Rental - Intro Offer
After that you can delete the helper custom column and your good to go.
Hope this helps!
Hey @kincaids,
You can use the following DAX formula to make a new column for the Rental Account Type:
Rental Account Type = IF([Down Payment] = 0, "Beginner Rental - Starter Pack", IF((DATEDIFF([1st Pmt Due Date],[Contract Date], month) = 2, "Beginner Rental - Intro Offer", "Advancing Rental"))
Uses the DATEDIFF and IF functions.
Hope this helps,
Alan
Thanks for your help, @alanhodgson! I'm having an issue creating the column though...
When I click on, "Show Error," it highlights the comma after the '2' at the end of the second line. I've tried a couple changes to no avail. Further assistance would be appreciated!
Hi @kincaids,
The DAX provided by @alanhodgson is to return one column in report instead of Query Editor. You can create a calculated column in report like this:
In your scenario, as you want to return the count value of Beginner Rentals, Advancing rentals, Starter Packs, Intro Offers, I would suggest you create four measures then place each measure in separate card visual.
Beginner Rentals = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental',DATEDIFF('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[1st Pmt Due Date],MONTH)=2),'Rental Accounts - scrental'[Down Payment]<>0) + CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental','Rental Accounts - scrental'[Down Payment]=0))
Advancing Rentals = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental',DATEDIFF('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[1st Pmt Due Date],MONTH)=1),'Rental Accounts - scrental'[Down Payment]<>0)
Starter Packs = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental','Rental Accounts - scrental'[Down Payment]=0))
Intro Offer = CALCULATE(COUNTROWS('Rental Accounts - scrental'),FILTER('Rental Accounts - scrental',DATEDIFF('Rental Accounts - scrental'[Contract Date],'Rental Accounts - scrental'[1st Pmt Due Date],MONTH)=2),'Rental Accounts - scrental'[Down Payment]<>0)
Best Regards,
Qiuyun Yu
Thanks for the help, @v-qiuyu-msft! I believe this is all right, but I'm getting a slight discrepency, and not sure why. I wanted to filter some of the data by my "current rentals", so I only see the count of instruments currently out on rent that were beginning rentals vs. advancing rentals. When I did a basic filter (as apposed to advanced filtering), I come up with a total number that is 4 short of the overall statistic:
I've tried to play with the visual level filter, but I think that's correct, so I'm not sure why the discrepancy. Any thoughts would be helpful. Let me know if I need to supply you with additional information (I'm happy to do it, but don't know what else you might need to figure it out).
Hi @kincaids,
How did you calculate Current Rentals? Please check if any specific filter apply to those two visuals's visual level filter.
Best Regards,
Qiuyun Yu
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |