Forum Discussion
Car Wash Report for Dates
Hi,
I'm reporting how many cars are washed in single a day and which cars are not washed for 2 or more days. For these I have a "Cars" table that have plate numbers and other car stuffs in it, and an other table that have dates and plate numbers for each car wash operation and lastly a date table that covers all dates.
Now I want to make a graph for each day that shows the number of cars that arent washed for more than 2 days.
ex:
- 17.10.2019 -> 24 cars not washed for 2+ days
- 18.10.2019 -> 21 cars not washed for 2+ days
- 19.10.2019 -> 16 cars not washed for 2+ days
- A bar chart would be good for the visualisation for this example.
And for a second question how can I list the cars that are washed for everyday.
Sample Data:
CARS TABLE:
| PLATE | BRAND |
| 34 CIP 397 | HYUNDAI |
34 CIP 413 | HYUNDAI |
34 CIP 420 | HYUNDAI |
34 CIP 440 | HYUNDAI |
34 CIR 840 | FIAT |
| 34 CIR 963 | HYUNDAI |
34 CIR 992 | HYUNDAI |
| 34 CIS 011 | HYUNDAI |
| 34 CIS 026 | HYUNDAI |
OPERATION TABLE:
| DATE | PLATE |
| 1.11.2019 | 34 CIS 026 |
| 1.11.2019 | 34 CIR 992 |
| 1.11.2019 | 34 CIR 840 |
| 2.11.2019 | 34 CIP 413 |
| 2.11.2019 | 34 CIP 397 |
| 2.11.2019 | 34 CIS 026 |
| 3.11.2019 | 34 CIS 026 |
Hi mkersoy_yon
I have modified some measures, also filter the report to show limited data.
You could check if the result fit your actual requirements.
modified measure
counts_washed_days = VAR n = CALCULATE ( DISTINCTCOUNT ( Operations[plate] ), FILTER ( ALLSELECTED ( Operations ), Operations[date] = MAX ( Operations[date] ) && [isblank] > 0 ) ) RETURN IF ( n = BLANK ()&&MAX(Operations[Date])<>BLANK(), 0, n )count_all = IF(MAX(Operations[Date])<>BLANK(),COUNT(Cars[Plate]))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- Mariusz
Community Champion
Hi mkersoy_yon
Try something like that
Unwashed = SUMX( CROSSJOIN( VALUES( Cars[PLATE] ), VALUES( 'Calendar'[Date] ) ), VAR __c0 = COUNTROWS( Operations ) VAR __c1 = CALCULATE( COUNTROWS( Operations ), PREVIOUSDAY( 'Calendar'[Date] ) ) VAR __c2 = CALCULATE( COUNTROWS( Operations ), PREVIOUSDAY( PREVIOUSDAY( 'Calendar'[Date] ) ) ) RETURN INT( NOT __c0 = __c1 || NOT __c0 = __c2 || __c0 = BLANK() ) )Washed = SUMX( CROSSJOIN( VALUES( Cars[PLATE] ), VALUES( 'Calendar'[Date] ) ), VAR __c0 = COUNTROWS( Operations ) VAR __c1 = CALCULATE( COUNTROWS( Operations ), PREVIOUSDAY( 'Calendar'[Date] ) ) VAR __c2 = CALCULATE( COUNTROWS( Operations ), PREVIOUSDAY( PREVIOUSDAY( 'Calendar'[Date] ) ) ) RETURN INT( __c0 = __c1 && __c0 = __c2 && NOT __c0 = BLANK() ) )Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution. 
v-juanli-msftCommunity Support
Hi mkersoy_yon
Is this your expected result?
day-1 = CALCULATE ( COUNT ( Operations[date] ), FILTER ( ALLSELECTED ( Operations ), Operations[plate] = MAX ( Operations[plate] ) && Operations[date] = MAX ( Operations[date] ) - 1 ) ) day-2 = CALCULATE ( COUNT ( Operations[date] ), FILTER ( ALLSELECTED ( Operations ), Operations[plate] = MAX ( Operations[plate] ) && Operations[date] = MAX ( Operations[date] ) - 2 ) ) isblank = [day-1]+[day-2] counts_washed_days = VAR n = CALCULATE ( DISTINCTCOUNT ( Operations[plate] ), FILTER ( ALLSELECTED ( Operations ), Operations[date] = MAX ( Operations[date] ) && [isblank] > 0 ) ) RETURN IF ( n = BLANK (), 0, n ) count_all = COUNT(Cars[plate]) count_unwashed_2days = [count_all]-[counts_washed_days]Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- parry2k
Super User
mkersoy_yon Please share sample pbix file using one drive/google drive to get you the solution. Remove any sensitive information before sharing.
Thank for your replies
Google Drive Link for PBIX in RAR
I tried both measurments but none of them solved the issue.
- In Mariusz 's answer measurements give only the row number of cross joined table.
- v-juanli-msft I tried your solution and actually i couldn't understand the filters in the measurements.
As you can guess I'm a newbie in DAX so i can think of the algorithm but cant do the measurements.
- v-juanli-msft
Hi mkersoy_yon
I have modified some measures, also filter the report to show limited data.
You could check if the result fit your actual requirements.
modified measure
counts_washed_days = VAR n = CALCULATE ( DISTINCTCOUNT ( Operations[plate] ), FILTER ( ALLSELECTED ( Operations ), Operations[date] = MAX ( Operations[date] ) && [isblank] > 0 ) ) RETURN IF ( n = BLANK ()&&MAX(Operations[Date])<>BLANK(), 0, n )count_all = IF(MAX(Operations[Date])<>BLANK(),COUNT(Cars[Plate]))Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. - v-juanli-msft
Hi mkersoy_yon
[counts_washed_days] means:
eg, at 2019/11/3, there are 10 cars have beed washed previous two days(they are washed at 2019/11/1 or 2019/11/2),
[count_all] means:
there are 164 cars total in your "car" table.
[count_unwashed_2days] means:
For every date point, how many cars which haven't been washed at least two days.
I have a doubt about your requirements, maybe i misunderstand something,
Please check if my assumption below is correct or my previous understanding is correct.
for example, at 2019/11/3, the unwashed cars(for 2+days) is which is not washed at 2019/11/3 and 2019/11/2, maybe 2019/11/1 and before it also wasn't washed too.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
