How to add Daily, weekly and Monthly Data

[Bhavyasree]

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.

[amitchandak]

@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]

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.

[v-tangjie-msft · Accepted solution]

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.