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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Community Support

Hi @Bhavyasree ,

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

(1) This is my test data.

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

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.

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

Frequent Visitor

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.

Community Support

Hi @Bhavyasree ,

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

(1) This is my test data.

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

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Microsoft Fabric & AI Learning Hackathon

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

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors