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

 

4 REPLIES 4
Highlighted
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

 

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.