topic Re: A measure to calculate missing submission of reports in DAX Commands and Tips

A measure to calculate missing submission of reports

NimaM — Fri, 26 Aug 2022 22:45:20 GMT

Hi,

We are running an event for two weeks. So each calendar date in this period has also an event day number, for instance, today is Event day 7.

All our stores should submit a daily sales report by the end of the day for their sale amount of YESTERDAY during this event (but not all do as expected). All these reported sales are stored in a FactDailySalesReports. So for each day, I know how many reports I have received with dollar value and etc.

Data Model: I have a FactDailySalesReports, connected to a Date table, connected to an EventDay table.

I need to create a report that shows Stores and the Number of Missing Reports in a table format or something. For instance, if today is the 7th day of the event and I expect to have 6 reports from every store, but since I don't, then I need an additional column to tell me how many are missing reports for each store up to this point.

Hopefully, this is clear enough.

Thanks,

Re: A measure to calculate missing submission of reports

daXtreme — Sun, 28 Aug 2022 07:31:47 GMT

Why don't you create a table that will, for each store, hold a record that will tell you the date of report submission, something like report id and the id of the store that submitted the report? It'll then be so easy to calculate what you want... Such a table could be hidden from the end user and only a measure exposed.

Re: A measure to calculate missing submission of reports

Anonymous — Tue, 30 Aug 2022 07:14:44 GMT

Hi NimaM ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure = var _today=TODAY() var _day= DATEDIFF( MAXX(FILTER(ALL('FactDailySalesReports'),'FactDailySalesReports'[stores]=MAX('FactDailySalesReports'[stores])),[Date]) ,_today,DAY) return IF( _day =0, "NO",_day &" "&"time are missing")

2. Result:

If you need pbix, please click here.

A measure to calculate missing submission of reports.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: A measure to calculate missing submission of reports

NimaM — Tue, 30 Aug 2022 18:11:35 GMT

Thanks, Liu,

It worked perfectly. 🙂