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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Bhavyasree
Frequent Visitor

How to add Daily, weekly and Monthly Data

Hi team,

 

I'm having Service_now_minutes column in my table. How to calculate Daily Service_now_minutes, Weekly Service_now_minutes and Monthly Service_now_minutes?

Thanks in advance.

1 ACCEPTED SOLUTION

Hi @Bhavyasree ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1670231652399.png

(2) We can create calculated columns.

 

Day = DAY(EOMONTH(DATE('Table'[Year],'Table'[Month],1),0))
Hour_daily = DIVIDE('Table'[monthly servicenow hours],'Table'[Day],0)
Hour_weekly = DIVIDE('Table'[monthly servicenow hours],4,0) 

 

(3) Then the result is as follows.

vtangjiemsft_3-1670232044559.png

 

Best Regards,

Neeko Tang

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

3 REPLIES 3
amitchandak
Super User
Super User

@Bhavyasree , Do you have a date, then you can join it with date table and analyze sum of that by day , month week etc

 

 

examples

 

Calendar =
Var _cal = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Week No" , if([Date]> Date(Year([Date]),1,1) , Quotient(datediff(Date(Year([Date]),1,2) , [Date], day) ,7)+1 ,1)
, "Year Week" ,Year([Date])*100 + if([Date]> Date(Year([Date]),1,1) , Quotient(datediff(Date(Year([Date]),1,2) , [Date], day) ,7)+1 ,1)

)
return
addcolumns(_cal ,
"Week Rank" , rankx(_cal, [Year week],,asc,dense) ,
"Week Name" , format(minx(filter(_cal,[Year Week] =earlier([Year Week])),[Date]), "dd-Mon") &" - "& format(maxx(filter(_cal,[Year Week] =earlier([Year Week])),[Date]), "dd-Mon")
)

 

DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145s

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Bhavyasree_0-1670050119747.jpeg

Please refer abouve snapshot .First i need to calucalte Dailyservicenow hours,Weekly Servicenow hrs and monthly servicenow hrs for above calculations. Cuurently i am having Servicenow hours column,Based on this how can i calculate Daily servicenow hrs,weekly hrs and monthly hrs.

 

Note: I am having monthly servicenow hours column,If it is fine then how can i convert this monthly hours into weekly hours and Daily hours.

 

Thank you so much for your reply.

Hi @Bhavyasree ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1670231652399.png

(2) We can create calculated columns.

 

Day = DAY(EOMONTH(DATE('Table'[Year],'Table'[Month],1),0))
Hour_daily = DIVIDE('Table'[monthly servicenow hours],'Table'[Day],0)
Hour_weekly = DIVIDE('Table'[monthly servicenow hours],4,0) 

 

(3) Then the result is as follows.

vtangjiemsft_3-1670232044559.png

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.