cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
1 ACCEPTED SOLUTION
Community Support

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 6
Frequent Visitor

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.

Community Support

[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.

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.

Community Support

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.

Community Support

``````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.

Community Champion

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.

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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.