cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Tevon713
Helper III
Helper III

Calculate Day per month per year and total

I need help, might be something simple. Trying to capture/ create measure per month per year and total overall.

 

Data Table

Site #Operating DayDate
Site 1201/1/2020
Site 2201/1/2020
Site 3201/1/2020
Site 1212/1/2020
Site 2212/1/2020
Site 3212/1/2020
Site 1203/1/2020
Site 2203/1/2020
Site 3203/1/2020
Site 1211/1/2021
Site 2211/1/2021
Site 3211/1/2021
Site 1192/1/2021
Site 2192/1/2021
Site 3192/1/2021
Site 1203/1/2021
Site 2203/1/2021
Site 3203/1/2021
Site 1201/1/2022
Site 2201/1/2022
Site 3201/1/2022
Site 1202/1/2022
Site 2202/1/2022
Site 3202/1/2022
Site 1223/1/2022
Site 2223/1/2022
Site 3223/1/2022

 

Desired output a single measure that user select time period and site to show operating day.

YearOp DayMonth
202020Jan
202021Feb
202020Mar
2020 Total61 
   
202121Jan
202119Feb
202120Mar
2021 Total60 
   
202220Jan
202220Feb
202222Mar
2022 Total62 
   
Total Overall183 
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@Tevon713 hey did you managed or asked for this calculation?

Op Day = DIVIDE(SUM('Table'[Operating Day]), DISTINCTCOUNT('Table'[Site #]))

View solution in original post

6 REPLIES 6
SpartaBI
Community Champion
Community Champion

@Tevon713 you just create this measure:

Op Day = SUM('Table'[Operating Day])
And I assume you have autodate time enabled in your model so you will have the year and month column to play with at the visual level from the automated hierarchy that is genreated. (In best practice you need a real date dimension table, but we don't need it make you work harder in this use case):
SpartaBI_0-1653575950663.png

 

2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Thanks @SpartaBI. Problem found is if user select more than 1 region or site, it just sum up those per month. That is not correct. 

 

With 2 sites selection

Tevon713_0-1653578551123.png

 

With 1 site (this is the correct)

Tevon713_1-1653578675400.png

 

 

 

Divide the sum by count of sites.

SpartaBI
Community Champion
Community Champion

@Tevon713 hey did you managed or asked for this calculation?

Op Day = DIVIDE(SUM('Table'[Operating Day]), DISTINCTCOUNT('Table'[Site #]))

Thank you again. Yes was exactly what I did.

SpartaBI
Community Champion
Community Champion

@Tevon713 Great!
Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up if you liked it 🙂

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors