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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
nandipk
New Member

Creating Dynamic Y-axis Values

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.

1 ACCEPTED SOLUTION
suparnababu8
Super User
Super User

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

View solution in original post

1 REPLY 1
suparnababu8
Super User
Super User

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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