Forum Discussion
Need help in creating an inflow/outflow chart using Power BI
Hi All,
I have been looking to create an inflow/outflow report with the below data where I am expecting a Bar Chart with Count of Numbers Opened Vs Count of Numbers Closed for each month and year.
I could achieve Opened Count Separately and Closed Count Separately. Not able to include them both in a Column Chart for showcasing the inflow vs outflow.
Any idea of achieving the same?
Thanks in Advance!
Arun
Anonymous ,
You may refer to the post below.
3 Replies
v-chuncz-msftCommunity Support
Anonymous ,
You may refer to the post below.
Hi v-chuncz-msft ,
I tried it but it is not giving me any result and I am getting an empty chart. I used the below formula for a Calendar table:
Date = ADDCOLUMNS(CALENDAR(DATE(2000,1,1),DATE(2050,12,31)),"Year", YEAR([Date]),"Monthnumber", FORMAT ( [Date], "MM" ),"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),"MonthNameShort", FORMAT ( [Date], "mmm" ),"MonthNameLong", FORMAT ( [Date], "mmmm" ),"DayOfWeekNumber", WEEKDAY ( [Date] ),"DayOfWeek", FORMAT ( [Date], "dddd" ),"DayOfWeekShort", FORMAT ( [Date], "ddd" ),"Quarter", "Q" & FORMAT ( [Date], "Q" ),"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))And created inflow and outflow using the below measures:Inflow =
VAR d =
MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ('Page 1 (2)'[Number]),
'Page 1 (2)'[Opened] = d
)
Outflow =
VAR d =
MAX('Date'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT('Page 1 (2)'[Number]),
'Page 1 (2)'[Closed] = d
)Am I missing something?
Thanks,
Arun
I figured out what I was doing as wrong. I need to ensure that the format of the columns should match one another.
FORMAT should MM/DD/YYYY for all the date fields even on the calendar for it to work.
I was trying to match MMM/YYYY with MM/DD/YYYY.
Now it has been corrected and it is working as it should.