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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
hanyfouda2023
Helper II
Helper II

Days Vending machine non operating

Hi helpers,

I have list of vending machines & database is contains Master tables for serial numbers & Sales tables contains "Sales , Qty"  I'm facing an issue that if one vending machine doesnt  work for one or several days i didnt dicover it.

So I need to know how to check if single or more vending machines not working

 

1 ACCEPTED SOLUTION

 

Daniel29195_0-1705839847979.png

please follow these steps : 

 step 1 

create datetable table : 

datetable = CALENDAR(MIN(transactions[Date ]), MAX(transactions[Date ]))





sample data used : 
Daniel29195_5-1705840035655.png

 

 
step 2 : 
link datetable new the transaction table via date column. 
Daniel29195_1-1705839901397.png

 

 

step3 : create this measure : 

Daniel29195_2-1705839934689.png

 

 

non working days =
var dates = ALL(datetable[Date])

var available_dates =
CALCULATETABLE(
    VALUES(transactions[Date ]),
    ALLSELECTED(datetable[Date])
)

var result =
EXCEPT(
    dates,
    available_dates
)

return  

CALCULATE(
    COUNTROWS(datetable),
    KEEPFILTERS(result)
)
step 4 :  create the visual 
Daniel29195_3-1705839970029.png

 

drag the following columns the visual .

 

 

final result : 
this will gives the non working hours per #serial .

Daniel29195_4-1705839997698.png

 

 

 

hope this helps .

 

best regards


 
 

View solution in original post

8 REPLIES 8
hanyfouda2023
Helper II
Helper II

Hi Daniel29195,

Thanks for your replay I tried it but it doesnt work , I need something like how many days this VM operating 

@hanyfouda2023 

are you able to share some data .

with a visual representation maybe on excel, of the desired output. 

 

this would help  understanding more your requirement. 

Here you are,

Master

Serial #
1
2
3
4
5
6
7
8
9
10
11

12

 

Transaction

Serial #Date AmountQty
101-Jan               10                  1
201-Jan               20                  2
301-Jan               50                  5
401-Jan               67                  7
501-Jan               87                  3
601-Jan             107                  6
701-Jan             127                  7
801-Jan             147                  8
901-Jan             167                  9
1001-Jan             187               10
1101-Jan             207               11
102-Jan             227               12
202-Jan             247               13
302-Jan             267               14
402-Jan             287               14
502-Jan             307               15
602-Jan             327               16
702-Jan             347               17
802-Jan             367               18
902-Jan             387               19
103-Jan             407               20
203-Jan             427               21
303-Jan             447               22
403-Jan             467               23
503-Jan             487               23
603-Jan             507               24
703-Jan             527               25
803-Jan             527               26

 

Daniel29195_0-1705839847979.png

please follow these steps : 

 step 1 

create datetable table : 

datetable = CALENDAR(MIN(transactions[Date ]), MAX(transactions[Date ]))





sample data used : 
Daniel29195_5-1705840035655.png

 

 
step 2 : 
link datetable new the transaction table via date column. 
Daniel29195_1-1705839901397.png

 

 

step3 : create this measure : 

Daniel29195_2-1705839934689.png

 

 

non working days =
var dates = ALL(datetable[Date])

var available_dates =
CALCULATETABLE(
    VALUES(transactions[Date ]),
    ALLSELECTED(datetable[Date])
)

var result =
EXCEPT(
    dates,
    available_dates
)

return  

CALCULATE(
    COUNTROWS(datetable),
    KEEPFILTERS(result)
)
step 4 :  create the visual 
Daniel29195_3-1705839970029.png

 

drag the following columns the visual .

 

 

final result : 
this will gives the non working hours per #serial .

Daniel29195_4-1705839997698.png

 

 

 

hope this helps .

 

best regards


 
 

Thanks Alot for your support , it works fine!

All I need to know how can I discover that Serial # 11 , 12 non performing in 02n January & serial numbers 9 , 10 , 11 non performing in 03rd January

Daniel29195
Super User
Super User

hello @hanyfouda2023 

 

try using the show items with no data.

 

this will show rows even if they don have values

Daniel29195_0-1705830537254.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors