Measure to get a count of records created in the last 6 months

[jdubs]

How can I create a measure to get a count of records created in the last 6 months and display this number in a Card visual?

I have a 'created on' date in my table that I have linked to a DimDate table.

[Anonymous]

Hi @jdubs ,

According to my understanding, you want to count rows when the date(maybe the endTime in DimDate table) is more than 6 months from the created_on date, right?

Here are my data sample:

You could use the following formula:

diffMonth =
DATEDIFF (
    SELECTEDVALUE ( CreatedDate[Created on] ),
    SELECTEDVALUE ( DimDate[endTime] ),
    MONTH
)

perProCount =
CALCULATE (
    COUNT ( DimDate[Pro] ),
    FILTER (
        ALL ( DimDate ),
        [diffMonth] <= 6
            && DimDate[Pro] = MAX ( DimDate[Pro] )
    )
)

totalCount =
CALCULATE (
    COUNT ( DimDate[Pro] ),
    FILTER ( ALL ( DimDate ), [diffMonth] <= 6 )
)

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[jdubs]

Hi,

Thanks. Not exactly. I just want a count of all records and then a count of records created in the last 6 months. This can be displayed in a multi-row card or a clustered chart.

E.g.

Total # of records = 3000

Records created in the last 6 months = 1500

[harshnathani]

Hi @jdubs ,

You can use these measures

Total Records = COUNTROWS('Table')

Total Records Last 180 days = 
var _lastdate = LASTDATE('Table'[Audit_Date])
RETURN
CALCULATE(COUNTROWS('Table'), FILTER(ALL(Dates), Dates[Date] > _lastdate - 180  && Dates[Date] < _lastdate))

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

HI @jdubs 

_count = CALCULATE( COUNTROWS(table),
                                                    FILTER(table,DATEDIFF(MIN(table[created on])-MAX(table[created on]))<=6)
                                                 )

[jdubs]

Hi Pranit,

Thanks. It's telling me too few arguments are being passed to DATEDIFF