How Many Records that the Date is in This Week

[Anonymous]

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

[VahidDM]

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  !!

[Anonymous]

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

[VahidDM]

@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  !!

[Anonymous]

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.

[VahidDM · Accepted solution]

@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  !!

[Anonymous]

@VahidDM Thank you so much for your help!