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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
hanyfouda2023
Helper II
Helper II

Check sales from selected number of machines

Dear helpers,

I have list of vending machines with exact sort of serial numbers with operating & closing dates , I need to check for example sales for 2023 came from how many vending machines like for example , total sales USD 1000 from 4 vending machines , below the database table 

Serial # Location Name Operating DateClosing DateStatus
1System 101-01-2330-12-23Active
2System 201-01-2330-12-23Active
3System 301-01-2330-12-23Active
4System 401-01-2330-12-23Active
5System 501-01-2330-12-23Active
6System 601-01-2330-12-23Active
7System 701-01-2330-12-23Active
8System 801-01-2330-12-23Active
9System 901-01-2330-12-23Active
10System 1001-01-2330-12-23Active
11System 1101-01-2330-12-23Active
12System 1201-01-2330-12-23Active
13System 1328-04-2230-05-23Inactive
14System 1427-04-2230-05-23Inactive

 

Thanks

1 ACCEPTED SOLUTION

hi, @hanyfouda2023 

based on your data it work see below image

Dangar332_0-1704194538506.png

 

i am taking year of your operating date and compute measure

 

measure =
Measure = COUNT('Table'[serial])

 

 

calculated column

Dangar332_0-1704194761759.png

 

you can download .pbix file click HERE 

View solution in original post

4 REPLIES 4
hanyfouda2023
Helper II
Helper II

Hi Danger323,

Thanks for your answer , when I apply it it gives same total number of Vending machines each year , for example 

2022      4 VM

2023     4 VM

 

hi, @hanyfouda2023 

based on your data it work see below image

Dangar332_0-1704194538506.png

 

i am taking year of your operating date and compute measure

 

measure =
Measure = COUNT('Table'[serial])

 

 

calculated column

Dangar332_0-1704194761759.png

 

you can download .pbix file click HERE 

Dear Danger 332 ,

Thanks it works now when I used operating date 

Dangar332
Super User
Super User

Hi, @hanyfouda2023 

make a new column of year based on operating date or closing date

 

year = Year(tablename[closing date])

                 or

year = Year(tablename[operating date])

 

 

then make new measure like below

 

count of machine =
  count(tablename[serial#])

 

drag your Year column and count of machine measure in table visual

you get your answer

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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