topic Re: Row count not counting rows on last day of the month in DAX Commands and Tips

Row count not counting rows on last day of the month

Rince91 — Tue, 11 Aug 2020 11:32:42 GMT

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

Re: Row count not counting rows on last day of the month

amitchandak — Tue, 11 Aug 2020 11:45:11 GMT

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Row count not counting rows on last day of the month

AntrikshSharma — Tue, 11 Aug 2020 11:47:30 GMT

Maybe you need to use ALL ( ServiceReview )

Re: Row count not counting rows on last day of the month

Rince91 — Tue, 11 Aug 2020 17:00:15 GMT

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.

Re: Row count not counting rows on last day of the month

Anonymous — Wed, 12 Aug 2020 12:34:27 GMT

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?

Re: Row count not counting rows on last day of the month

Rince91 — Mon, 17 Aug 2020 08:43:50 GMT

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!