cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Soren Regular Visitor
Regular Visitor

Counting Number of Months in a date range

Hi Everybody

I am trying to count number of months that we used a specific type of car in a project.

In the below table, I have Date- asset which are list of days that we used an asset, and in the second columns we Have the asset codes. For some assets like Cw- PO, clients pay a monthly cost , regardless of number of days we used that asset in a month. I just wan to count the number of months we used that asset. Not sure what is wrong with my formula, but I cant get the right result!

 

Month Count = CALCULATE(DISTINCTCOUNT('MRMS'[Date-Asset].[Month]),FILTER('MRMS ','MRMS Asset Hours'[WorkOrderCode]="CW - PO 4800058329"))

 

Your help would be highly appreciated!

 

Capture5.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Jessica_Seiya Established Member
Established Member

Re: Counting Number of Months in a date range

You can try this.

 

Create a column with the following DAX to extract month information.

 

Month = FORMAT(MARMS[Date],"MMMM")

Then use a measure with the following DAX

 

CountNo = DISTINCTCOUNT(MARMS[Month])

2018-08-09_9-41-22.png

 

View solution in original post

4 REPLIES 4
Seward12533 New Contributor
New Contributor

Re: Counting Number of Months in a date range

Bunch of ways.

If you have a date table with a lolumn for year month . COUNTROWS(VALUES(Date[year month])

Using datediff
DATEDIFF(MIN(Table[DATE-ASSET]],MAX(Table[DATE-ASSET]),MONTH)
Soren Regular Visitor
Regular Visitor

Re: Counting Number of Months in a date range

Thanks for the respose. 

I applied the below formula, but the result is 55K which is absolutely wrong !

 

Month Count = countrows(VALUES('MRMS '[Date-Asset].[Month]))

Jessica_Seiya Established Member
Established Member

Re: Counting Number of Months in a date range

You can try this.

 

Create a column with the following DAX to extract month information.

 

Month = FORMAT(MARMS[Date],"MMMM")

Then use a measure with the following DAX

 

CountNo = DISTINCTCOUNT(MARMS[Month])

2018-08-09_9-41-22.png

 

View solution in original post

Seward12533 New Contributor
New Contributor

Re: Counting Number of Months in a date range

The datediff Should work. I use it all the time. But needs to have the right filter context. Are you displaying this measure in context of a specific Work order or in an applied filter context with slicers? Are you displaying in matrix or table where the rows are from a different table than the dates. Do you have any rows with blanks for the deliver dates? Might be picking up the number of months between the last date and date 0 (1/1/1900)

Wite a few measures for max and min dates and display in a multi card visual to see what is going on. Can you share a pic of your model or share a sample for? Might need to wrap in a calculate to force filter context.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 309 members 3,128 guests
Please welcome our newest community members: