Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
Just need a small help.
I was trying to create a stacked bar Graph where the X-axis is year and week. Y-Axis is number of NCEP No open in that week, Number of NCEPS Open in previous weeks that are not closed at the start of that week. This essentially tells me how many cases are open at the start of each week (Earliest time on monday). My unique identifier is NCPE No, Each NCEP No has initiaiton_date, ncep_closed_date. Each NCEP No will always have initiation_date but will only have ncep_closed_date if the case is closed, if not this will be null. A initiation_date can have many NCEP No open on that date. Any ideas? on how i can achieve this please. I have tried everything I learned but am stuck on this. BTW i started learning Power BI only a few weeks ago, apologies if this is simple or stupid question. I would greatly appreciate your help.
Solved! Go to Solution.
Hi @nandipk
I am assuming your table having this columns NCEP No, initiation_date, and ncep_closed_date
--> Create a date table based on initiation_date column.
--> Then add a claculated column to this date table to get year number and week number
YearWeekNo = YEAR([Date]) & "-" & WEEKNUM([Date], 2)
--> Then create a measure to count the number of NCEPs open at the start of each week
OpenNCEPs =
CALCULATE(COUNTROWS('NCEP Table'),
'NCEP Table'[initiation_date] <= MAX('Date'[Date]) &&
(ISBLANK('NCEP Table'[ncep_closed_date]) || 'NCEP Table'[ncep_closed_date] > MAX('Date'[Date])))
--> Then create one more measure to count the number of new NCEPs opened in each week
NewNCEPs =
CALCULATE(COUNTROWS('NCEP Table'),
'NCEP Table'[initiation_date] >= MIN('Date'[Date]) &&
'NCEP Table'[initiation_date] <= MAX('Date'[Date]))
--> Then take stacked barchart drag YearWeek calcuated column to X-axis from date table. Drag OpenNCEPs and NewNCEPs on Y-axis
Let me know if it works
Hi @nandipk
I am assuming your table having this columns NCEP No, initiation_date, and ncep_closed_date
--> Create a date table based on initiation_date column.
--> Then add a claculated column to this date table to get year number and week number
YearWeekNo = YEAR([Date]) & "-" & WEEKNUM([Date], 2)
--> Then create a measure to count the number of NCEPs open at the start of each week
OpenNCEPs =
CALCULATE(COUNTROWS('NCEP Table'),
'NCEP Table'[initiation_date] <= MAX('Date'[Date]) &&
(ISBLANK('NCEP Table'[ncep_closed_date]) || 'NCEP Table'[ncep_closed_date] > MAX('Date'[Date])))
--> Then create one more measure to count the number of new NCEPs opened in each week
NewNCEPs =
CALCULATE(COUNTROWS('NCEP Table'),
'NCEP Table'[initiation_date] >= MIN('Date'[Date]) &&
'NCEP Table'[initiation_date] <= MAX('Date'[Date]))
--> Then take stacked barchart drag YearWeek calcuated column to X-axis from date table. Drag OpenNCEPs and NewNCEPs on Y-axis
Let me know if it works
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
96 | |
92 | |
83 | |
69 |
User | Count |
---|---|
160 | |
125 | |
116 | |
110 | |
95 |