DAX formula question - getting a count of prior day records

[Anonymous]

I do not know why this is not working, so maybe someone can help.  I have a table of 32,000+ transaction records and one of the columns is Created, which shows the date and time that the transaction was created.  I have a calculated column to filter out the time and just give me the date.  

I now was another calculated column that will show me the prior dates rowcount, if that make sense.  So if I had 20 transactions yesterday, I will get a total of 20, and if that prior day was zero, then I will get a total of zero, and so on.  I have this, but it is not working for some reason: Yesterday = CALCULATE(COUNTROWS(qryGetMEWO),FILTER(qryGetMEWO,qryGetMEWO[ShortDate].[Date]=DATEADD(qryGetMEWO[ShortDate].[Date],-1,DAY)))

Maybe I need a measure and not a calculated column?  Ultimately I want to plot each prior day's total on a line chart and do a trendline.

Thanks for the information

[Greg_Deckler]

Yeah, if you are doing that in a column, then your filter context isn't going to be correct starting out, you would want to do an ALL and not sure why you are doing a calculate that way, I think what you want would be along the lines of:

Yesterday = COUNTROWS(FILTER(ALL(qryGetMEWO),qryGetMEWO[ShortDate].[Date]=DATEADD(qryGetMEWO[ShortDate].[Date],-1,DAY))

[Anonymous]

Should I be using a measure or calculated column?  I would think a measure because a column would put a value in each record, correct?  I also tried your formula: YesterdayWO = COUNTROWS(FILTER(ALL(qryGetMEWO),qryGetMEWO[ShortDate].[Date]=DATEADD(qryGetMEWO[ShortDate].[Date],-1,DAY))) both ways and it is like mine and coming up with no values.  Would it be because the DATEADD is evaluating also with a time and it is not an exact match to the ShortDate column?

[Greg_Deckler · Accepted solution]

I don't know, tough to say. No sample data and no posting of what you are going for. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Actually what I ended up doing is create a separate calculated table using dates and it is correctly doing a count of how many records match the qryMEWO table based on the matching date.  And my line graph is also able to drill down by year, quarter, month, etc.

Thanks for the responses