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

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

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
Impactful Individual
Impactful Individual

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
Impactful Individual
Impactful Individual

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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