cancel
Showing results for
Did you mean:
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 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:

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?

1 ACCEPTED SOLUTION
Community Support

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

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.
6 REPLIES 6
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!

Solution Supplier

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",

Uses the DATEDIFF and IF functions.

Hope this helps,

Alan

Helper II

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!

Community Support

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

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

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

Community Support

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.

Announcements