Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
In my data base, each line corresponds to a given item. Each item has a begining date and a end date in 2 differents columns.
I would like to compare the quantity of begining and end date in a period (month by month) in the same visual.
I tried severeal approches, but I can't do what I need.
Item Begining end
A dec 2021 jan 2022
B jan 2022 fev 2022
In that exemple, I would like to have a chart with january 1 begining & 1 end // february 0 begining & 1 end
Do you have an idea on how I can do that ?
Thanks for your help.
Solved! Go to Solution.
Hi @Lili123 ,
Below is my solution.
1.Create the yearmonth number column for the begin date and the end date.
BeginYearMonth = VALUE(FORMAT([Begin date],"YYYYMM"))
EndYearMonth = VALUE(FORMAT([End date],"YYYYMM"))
2.Create a Yearmonth table.
Yearmonth =
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( DATE ( 2021, 11, 1 ), DATE ( 2022, 2, 28 ) ),
"YearMonthNumber", VALUE ( FORMAT ( [Date], "YYYYMM" ) ),
"YearMonth", FORMAT ( [Date], "YYYY-MMM" )
)
)
3.Create a measure to return 1 when the YearMonthNumber in the Yearmonth table is between begin and end.
Measure =
VAR _NUM =
MAX ( 'Yearmonth'[YearMonthNumber] )
VAR _BE =
MAX ( 'Table'[BeginYearMonth] )
VAR _EN =
MAX ( 'Table'[EndYearMonth] )
RETURN
IF ( _BE <= _NUM && _NUM <= _EN, 1 )
4.Create another measure to sum up.
Measure 2 = SUMX('Table',[Measure])
Here's the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Lili123 ,
Below is my solution.
1.Create the yearmonth number column for the begin date and the end date.
BeginYearMonth = VALUE(FORMAT([Begin date],"YYYYMM"))
EndYearMonth = VALUE(FORMAT([End date],"YYYYMM"))
2.Create a Yearmonth table.
Yearmonth =
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( DATE ( 2021, 11, 1 ), DATE ( 2022, 2, 28 ) ),
"YearMonthNumber", VALUE ( FORMAT ( [Date], "YYYYMM" ) ),
"YearMonth", FORMAT ( [Date], "YYYY-MMM" )
)
)
3.Create a measure to return 1 when the YearMonthNumber in the Yearmonth table is between begin and end.
Measure =
VAR _NUM =
MAX ( 'Yearmonth'[YearMonthNumber] )
VAR _BE =
MAX ( 'Table'[BeginYearMonth] )
VAR _EN =
MAX ( 'Table'[EndYearMonth] )
RETURN
IF ( _BE <= _NUM && _NUM <= _EN, 1 )
4.Create another measure to sum up.
Measure 2 = SUMX('Table',[Measure])
Here's the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Lili123 ,The information you have provided is not making the problem clear to me. Can you please explain with an example?
But check if these two blogs can help
Appreciate your Kudos.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |