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