Forum Discussion
Weekly visualization
Dear Experts,
In my previous post https://community.fabric.microsoft.com/t5/Developer/Utiisation-per-week/td-p/3730765 I was helped by Anonymous the result was amazing.
I have now another question on how to create a visualization every week if I filter by the utilization.
If I run a filter on the total utilization less than 70%, I expect to have this:
- on week 29/02/2024, grade 1 = 3 (Arif, Jon, Sapri), grade 2 = 4 (Bowo, else), and so on
- on week 03/03/2024, grade 1 = 3 (Arif, Jon, Sapri), grade 2 = 3 (Bowo, Joko, Supri), grade 3 = 1
- on week 10/03/2024, grade 1 = 3 (Jon, Sapri, Upik), grade 2 = 4, grade 3 = 1
and next question is, how can I visualize that on a chart?
I managed to solve this by duplicating the table and running the group by in power-query.
4 Replies
kangkopi15 , Better create a date table with Week Date/Number in the table and Have Utilization % plot with That
Join dat if date table with date of your table
example
ADDCOLUMNS( //CALENDAR(EOMONTH(_min, -1*month(_min)) +1, EOMONTH(TODAY(),-1)) CALENDAR(date(2018,01,01), date(2021,10,31)) ,"Year", YEAR([Date]) ,"Month No", MONTH([Date]) , "Qtr No", QUARTER([Date]) ,"Month",FORMAT([Date], "mmmm") , "Month Year", FORMAT([Date], "mmm-yyyy") ,"Qtr", FORMAT([Date],"YYYY-\QQ") ,"Month Year Sort", Year([Date])*100 + Month([Date]) , "Year Week No", YEAR([Date])*100 + WEEKNUM([Date],2) , "Week No", WEEKNUM([Date],2) , "Week Day", FORMAT([Date], "ddd") , "Weekday no", WEEKDAY([Date],11) , "Start Of Month", EOMONTH([Date],-1)+1 , "End of Month", EOMONTH([Date],0) , "Start of Year", EOMONTH([Date],-1* MONTH([Date]))+1 , "End of Year", EOMONTH([Date],12 -1* MONTH([Date])) , "Start of Qtr", var _rem= if(MOD(MONTH([Date]),3)=0,3,MOD(MONTH([Date]),3)) RETURN EOMONTH([Date], -1*_rem)+1 , "End of Qtr", var _rem= if(MOD(MONTH([Date]),3)=0,3,MOD(MONTH([Date]),3)) RETURN EOMONTH([Date], 3- _rem) ,"Week Start Date", [Date]- WEEKDAY([Date],11)+1 ,"Week End Date", [Date]+7- WEEKDAY([Date],11) , "Start of FY", Date(if(month([Date]) <4,Year([Date])-1, Year([Date])),4,1) , "End of FY", Date(if(month([Date]) <4,Year([Date]), Year([Date])+1),3,31) )If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.hello amitchandak , try with this pbix file
https://drive.google.com/file/d/1yq2diU0JdbLvIgmjTo2vdhv5sklTqWta/view?usp=drive_link
hi everyone, appreciate any suggestions or pieces of advice
I managed to solve this by duplicating the table and running the group by in power-query.
