cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arythedj
Helper I
Helper I

How Many Records that the Date is in This Week

Hi there,

I have a data like the below:

arythedj_4-1629961747852.png

This is what I would like to achieve in Power BI 

arythedj_5-1629961818795.png

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

1 ACCEPTED SOLUTION
VahidDM
Community Champion
Community Champion

@arythedj 

 

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 VahidDM_0-1630023120472.png !!

View solution in original post

6 REPLIES 6
VahidDM
Community Champion
Community Champion

Hi @arythedj 

 

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 VahidDM_0-1629965612889.png !!

 

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
Community Champion
Community Champion

@arythedj 
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 VahidDM_0-1629981732293.png !!

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.

arythedj_0-1630017704225.png

 

VahidDM
Community Champion
Community Champion

@arythedj 

 

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 VahidDM_0-1630023120472.png !!

View solution in original post

@VahidDM Thank you so much for your help!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors