Count totals for current date and previous date

[Anonymous]

Report Date	Cat
17/03/20	Correct
17/03/20	Wrong
17/03/20	Deduct
17/03/20	Missing
06/03/20	Deduct
06/03/20	Correct
06/03/20	Wrong
25/09/19	Correct
15/09/19	Missing

Good eveneing/afternoon/morning all,

Based on the above table, i would like to create two measures to count the 'Cat' for the current (17/03/20) & previous (06/03/20) 'Report Date' where 'Cat' does note equal "Correct".

The data within the table above will be updated frequently and new 'Report Dates' will be added. The measures will need
to dynamically understand what is the date of 'Current Report Date' & 'Last Report Date'.

Based on the above table the two measures will provide the following result:-
Current report = 3
Last Report = 2

Ultimately i want to deduct the difference of the 'Current Report' and 'last report' and add that value to a card.

Any help will be overwhelmingly appreciated

[amitchandak]

@Anonymous , witch help from a date calendar

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))


This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

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.

[Anonymous]

Thanks mahoneypat, your method worked wonderfully.

parry2k & amitchandak, thanks massivley for your contribution. I will eventually try to incorparate a date dimension as you suggested, as it sounds like best practice.

[parry2k]

@Anonymous solving a problem is one thing,  following best practice and having a scalable solution is another thing. Always always when you are working with dates, have a date dimension in your model, even if you are not working with dates, still have one, I haven't seen any report which is not have anything to do with dates. My 2 cents and good luck.

[mahoneypat · Accepted solution]

These expressions will generate the values you are looking for in your card visuals.

Current Report Date = var __currentdate = MAX('Table'[Report Date])

return CALCULATE(COUNTROWS('Table'), 'Table'[Cat]<>"Correct", 'Table'[Report Date]=__currentdate)

Last Report Date = var __currentdate = MAX('Table'[Report Date])

var __lastdate = CALCULATE(MAX('Table'[Report Date]), 'Table'[Report Date]<__currentdate)

return CALCULATE(COUNTROWS('Table'), 'Table'[Cat]<>"Correct", 'Table'[Report Date]=__lastdate)

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi mahoneypat!

Many many thanks for this. I am currently unable to try this for a few days.

I will leave some feedback as soon as i can.

Do you know if this method will work with a slicer?

As an example, I have another column for 'Item' (This contains one of either, item1, item2, item3, item4, item5, item6). Would i be able to use a slicer to display the result of 'Last Report Date' for each individual item in a Card?

Thanks again for all of your help

[mahoneypat]

Yes.  Those measures should work with slicers and in a table visual.  Please let me know if not.

If this works for you, please mark it as solution.  Kudos are appreciated too.

Regards,

Pat

[parry2k]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

Once you have a date dimension in your model, you can take advantage of all time intelligent functon and all these period based calculations will be super easy.

Previous Day = CALCULATE ( COUNTROWS ( Table ), DATEADD ( DateTable[Date], -1, DAY ), <<other filter condition>> )


Previous Day = CALCULATE ( COUNTROWS ( Table ), PREVIOUSDAY ( DateTable[Date]), <<other filter condition>> )

Let's follow the best practice for a more scalable solution and take advantage of Time Intelligence function. Why not to make calculations complicated?

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Hey parry2k & amitchandak,

Many thanks for your suggestions. I think adding a date dimension is probably the desired long term solution.

I will hopefully get some time to try it out later this week and give you some feedback. (after i have worked out a quick short term solution).

Many thanks again for taking the time to help me out