Row count not counting rows on last day of the month

[Rince91]

Im using the below measure to count the number of rows in the last month.

However for some reason it is missing some rows that occur on the 31st July.

I cant figure this one out as to why. Any thoughts?

MoM Growth = 

VAR StartDate = EOMONTH(TODAY(),-2)+1
VAR EndDate = EOMONTH(TODAY(),-1)

RETURN
CALCULATE(
DISTINCTCOUNT(ServiceReview[Interaction Ref]),
Filter(
ADDCOLUMNS(
ServiceReview,
"Last Month", IF(ServiceReview[CLOSE_TIME_Month_Year] >= StartDate
&& ServiceReview[CLOSE_TIME_Month_Year] <= EndDate, "Yes", "No")),[Last Month] = "Yes"))+0

[Anonymous]

MoM Growth =
VAR StartDateInclusive =
    EOMONTH(
        TODAY(),
        -2
    ) + 1
VAR EndDateExclusive =
    EOMONTH(
        TODAY(),
        -1
    ) + 1
RETURN
    CALCULATE(
        DISTINCTCOUNT( ServiceReview[Interaction Ref] ) + 0,
        KEEPFILTERS( StartDateInclusive <= ServiceReview[CLOSE_TIME_Month_Year] ),
        KEEPFILTERS(  ServiceReview[CLOSE_TIME_Month_Year] < EndDateExclusive )
    )

What about this?

[AntrikshSharma]

Maybe you need to use ALL ( ServiceReview )

[amitchandak]

@Rince91 , Do you have a timestamp in date. in that case, you need at timestamp in end date

VAR EndDate = EOMONTH(TODAY(),-1) +time(23,59,59)

Or create date column without timestamp

Date = [CLOSE_TIME_Month_Year].date

and us time intelligence

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Rince91 · Accepted solution]

So i solved the issue.

There was an issue with how the customer number was filtering. Some instances of the number were appearing as text wheras others were formatted as a number.

Fixed that formatting then implemented the time intelligence that @amitchandak suggested and it works great!

[Rince91]

This is great thanks. Unfortunately it didnt fix the issue...

I've done what you suggested created a Date = [CLOSE_TIME_Month_Year].date column.

Followed your webinar and created a date table and set up the relation ship.

However ther error persists. It is still missing one particular row on the 31st of july. 

This is the row that is missing, there are other rows for other customers, but this is one i know for sure isnt showing up correctly in the counts/visuals.

I have a column with the unique 5 digit customer number. I then have a column with a unique ticket refference, i have then created a closed ticket time column as you suggested.

The report is supposed to filter to a specific customer number then count the unique ticket references that occured last month.

I think there may be something wrong with the data itself as i can see the row in the raw data table, but it wont show in any visuals or measures. The date column is generating correctly.