Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |