Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mkersoy_yon
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:

PLATEBRAND
34 CIP 397HYUNDAI

34 CIP 413

HYUNDAI

34 CIP 420

HYUNDAI

34 CIP 440

HYUNDAI

34 CIR 840

FIAT
34 CIR 963HYUNDAI

34 CIR 992

HYUNDAI
34 CIS 011HYUNDAI
34 CIS 026HYUNDAI

 

OPERATION TABLE:

DATEPLATE
1.11.201934 CIS 026
1.11.201934 CIR 992
1.11.201934 CIR 840
2.11.2019

34 CIP 413

2.11.201934 CIP 397
2.11.201934 CIS 026
3.11.201934 CIS 026
1 ACCEPTED SOLUTION

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]))

Capture4.JPGCapture5.JPG

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.

 

View solution in original post

6 REPLIES 6
mkersoy_yon
Frequent Visitor

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.

 

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),

Capture6.JPG

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

 

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]))

Capture4.JPGCapture5.JPG

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

Hi @mkersoy_yon 

Is this your expected result?

Capture9.JPG

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.

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

 

parry2k
Super User
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.