Forum Discussion
Work down curve
Need help with making a work down curve.
| PEL_ID | CREDIT DATE | DEPARTMENT |
| 101 | 2019-12-19 | A |
| 102 | 2020-01-11 | B |
| 103 | 2020-02-09 | A |
| 104 | 2020-03-22 | |
| 105 | ||
| 106 |
PELS with no credit date has not been completed yet.
Need a formula to get the following table:
| DATE | PELS LEFT |
| JAN-20 | 5 |
| FEB-20 | 4 |
| MAR-20 | 3 |
| APR-20 | 2 |
| MAY-20 | |
| JUNE-20 | |
| JUL-20 | |
| AUG-20 | |
| SEP-20 | |
| OCT-20 | |
| NOV-20 | |
| DEC-20 |
I need to filter by department too. That's the major issue I am facing. Even if I manage to create a new table with the above columns, I cannot filter by department as there is no relation between the two tables.
Any help will really be appreciated! Thank you 🙂
Hi Anonymous ,
Try this:
1. Create a Calendar table.
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ), "YearMonth", FORMAT ( [Date], "YYYYMM" ), "Month-Year", FORMAT ( [Date], "MMM-YY" ) )2. Sort "Month-Year" column by "YearMonth" column.
3. Create the relationship between your fact table and Calendar table.
4. Create a Measure like so:
PELS LEFT = VAR CurrentDate = MAX ( 'Calendar'[Date] ) VAR CurrentYear = YEAR ( CurrentDate ) VAR CurrentMonth = MONTH ( CurrentDate ) VAR FirstDay = DATE ( CurrentYear, CurrentMonth, 1 ) RETURN IF ( MAX ( 'Calendar'[Month-Year] ) <> BLANK (), CALCULATE ( COUNT ( 'Table'[PEL_ID] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[CREDIT DATE] = BLANK () || 'Table'[CREDIT DATE] >= FirstDay ) ) )5. Create a Table visual and a Clustered column chart.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- Do you mean a burn down chart? What is the expected output?
Hi Anonymous ,
Try this:
1. Create a Calendar table.
Calendar = ADDCOLUMNS ( CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ), "YearMonth", FORMAT ( [Date], "YYYYMM" ), "Month-Year", FORMAT ( [Date], "MMM-YY" ) )2. Sort "Month-Year" column by "YearMonth" column.
3. Create the relationship between your fact table and Calendar table.
4. Create a Measure like so:
PELS LEFT = VAR CurrentDate = MAX ( 'Calendar'[Date] ) VAR CurrentYear = YEAR ( CurrentDate ) VAR CurrentMonth = MONTH ( CurrentDate ) VAR FirstDay = DATE ( CurrentYear, CurrentMonth, 1 ) RETURN IF ( MAX ( 'Calendar'[Month-Year] ) <> BLANK (), CALCULATE ( COUNT ( 'Table'[PEL_ID] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[CREDIT DATE] = BLANK () || 'Table'[CREDIT DATE] >= FirstDay ) ) )5. Create a Table visual and a Clustered column chart.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
