Work down curve

[Anonymous]

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 🙂

[Icey · Accepted solution]

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.

[Greg_Deckler]

Do you mean a burn down chart? What is the expected output?

[Anonymous]

Yeah burn down chart. 

The output is basically the second table in the form of a chart.