Creating Dynamic Y-axis Values

[nandipk]

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.

[suparnababu8 · Accepted 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