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

1 ACCEPTED SOLUTION

Accepted Solutions
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])`

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

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])`

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.