Forum Discussion
Inflow and outflow
Hello,
I am having 2 colums one for item Purchased date and another for item sold date.
I want to see how many items were purchased and how many were sold in Month n Month basis (month wise trend bar.)
I want both line\bar in same chart.
How i can achieve this.
Hi, NithinBN
If you have Dim-Date-Table, you can connect it with your main table with two relationship lines.
One is active with purchase date column, and the other is inactive with sold date column.
By using the active relationship, you can create the measure to distinctcount the item numbers purchased.
By using the inactive relationship, you can create
calculate ( your measure, userelationship ( datecolumn, sold date column))
to distinctcount the item numbers sold.
Please check the below picture and the link down below (sample pbix file)
all measures are in the sample pbix file.
https://www.dropbox.com/s/k2pkh8b5qtn79cl/NithinBN.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Hi NithinBN ，
You could try the following steps:
base table :
1.Create month table:
MonthTable = VAR FullCalendar = ADDCOLUMNS ( CALENDAR ( "2019/12/1", "2021/1/31" ), "Month Number", MONTH ( [Date] ), "Year", YEAR ( [Date] ), "Year-Month", LEFT ( FORMAT ( [Date], "yyyyMMdd" ), 6 ), "Month Name", FORMAT ( MONTH ( [Date] ), "MMM" ), "Year-MonthName", YEAR ( [Date] ) & " " & FORMAT ( MONTH ( [Date] ), "MMM" ) ) RETURN SUMMARIZE ( FullCalendar, [Month Number], [Year], [Year-Month], [Year-MonthName] )Step2，create the following measure on month table:
pcount = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),MAX(MonthTable[Year])=YEAR('Table'[Purchased Date])&&MAX(MonthTable[Month Number])=MONTH('Table'[Purchased Date])))scount = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),MAX(MonthTable[Year])=YEAR('Table'[Sold date])&&MAX(MonthTable[Month Number])=MONTH('Table'[Sold date])))Then create visualization,you will get what you want:
Wish it is helpful for you!
Best Regards
Lucien
3 Replies
NithinBN , You need to create a common date table and join both date with that. One join will be inactive. You have to use userelationship in measure to use that join.
Refer to my hr blog , to check how to use two dates
Hi, NithinBN
If you have Dim-Date-Table, you can connect it with your main table with two relationship lines.
One is active with purchase date column, and the other is inactive with sold date column.
By using the active relationship, you can create the measure to distinctcount the item numbers purchased.
By using the inactive relationship, you can create
calculate ( your measure, userelationship ( datecolumn, sold date column))
to distinctcount the item numbers sold.
Please check the below picture and the link down below (sample pbix file)
all measures are in the sample pbix file.
https://www.dropbox.com/s/k2pkh8b5qtn79cl/NithinBN.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Hi NithinBN ，
You could try the following steps:
base table :
1.Create month table:
MonthTable = VAR FullCalendar = ADDCOLUMNS ( CALENDAR ( "2019/12/1", "2021/1/31" ), "Month Number", MONTH ( [Date] ), "Year", YEAR ( [Date] ), "Year-Month", LEFT ( FORMAT ( [Date], "yyyyMMdd" ), 6 ), "Month Name", FORMAT ( MONTH ( [Date] ), "MMM" ), "Year-MonthName", YEAR ( [Date] ) & " " & FORMAT ( MONTH ( [Date] ), "MMM" ) ) RETURN SUMMARIZE ( FullCalendar, [Month Number], [Year], [Year-Month], [Year-MonthName] )Step2，create the following measure on month table:
pcount = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),MAX(MonthTable[Year])=YEAR('Table'[Purchased Date])&&MAX(MonthTable[Month Number])=MONTH('Table'[Purchased Date])))scount = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),MAX(MonthTable[Year])=YEAR('Table'[Sold date])&&MAX(MonthTable[Month Number])=MONTH('Table'[Sold date])))Then create visualization,you will get what you want:
Wish it is helpful for you!
Best Regards
Lucien
