topic Re: How Many Records that the Date is in This Week in DAX Commands and Tips

How Many Records that the Date is in This Week

Anonymous — Thu, 26 Aug 2021 07:12:14 GMT

Hi there,

I have a data like the below:

This is what I would like to achieve in Power BI

Driver Created is simple, it's just a count of driver_id

But I am struggling to figure out on how to create a measure to count the records in column E "First Job Date" that are in between column C "Week Starting Created" and column D "Week Ending Created"

Really appreciate if someone can help me.

Many thanks in advance.

Regards,

Ary

Re: How Many Records that the Date is in This Week

VahidDM — Thu, 26 Aug 2021 08:15:11 GMT

Hi Anonymous

Try this measure and add it to your table:

Started first job = calculate(COUNTROWS('Table'),filter(all('Table'),'Table'[First Job Date]>=max('Table'[Week Starting Created]&&'Table'[First Job Date]<=max('Table'[Week Ending Created]))))

Add it to the table

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos !!

Re: How Many Records that the Date is in This Week

Anonymous — Thu, 26 Aug 2021 09:49:43 GMT

Thanks VahidDM

Works like a charm.

I have slicers in this report.

Slicer fields are "Zone Name" and "Driver Region"

These 2 fields sit under the same table.

Is there a way to make the measure to not ignore the slicer selection?

Regards,

Ary

Re: How Many Records that the Date is in This Week

VahidDM — Thu, 26 Aug 2021 12:42:39 GMT

Anonymous
Yes, change that all('Table') to 'Table' in the measure I shared before and try it.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos !!

Re: How Many Records that the Date is in This Week

Anonymous — Thu, 26 Aug 2021 23:15:21 GMT

Thanks VahidDM

Apparently after removing ALL, the result is now filtered to the row level of the report (Week Starting Created).

See below the red box is without ALL, the first one was reduced from 36 to 21 because the result is now filtered to Week Starting Created of 23rd August.

Re: How Many Records that the Date is in This Week

VahidDM — Fri, 27 Aug 2021 00:13:57 GMT

Anonymous

change that all('Table') to ALLEXCEPT('Table','Table'[Zone Name],'Table'[Driver Region]) and test those slicers.

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos !!

Re: How Many Records that the Date is in This Week

Anonymous — Fri, 27 Aug 2021 00:23:57 GMT

VahidDM Thank you so much for your help!