cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kincaids
Helper II
Helper II

Can't figure out the proper syntax for a new measure

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 Date1st Pmt Due DateDown Payment
8/12/201310/12/201317.12
9/1/201610/1/20160
7/2/20149/2/201417.16
8/29/20159/29/20150
12/2/20131/2/2014178.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:

  1. How many rentals are Beginner Rentals and how many are Advancing rentals?
  2. Of the Beginning Rentals, how many are Starter Packs and how many are Intro Offers?

Thanks for your help!

1 ACCEPTED 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:

 

z1.PNG

 

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)

z2.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Casteless
Helper I
Helper I

 

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...

 

custom_column.png

 

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:

 

z1.PNG

 

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)

z2.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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:

 

 current_rentals.pngrental_breakdown.png

 

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors