DAX Measure to create burndown table

[manojsv16]

Hello guys,

I am having trouble in creating a measure to populate below burndown table(output table) in Power BI. 

I wanted to create a measure to replicate the following table. I have provided the acutal data(input table) below. I don't have a date field to use dax intelligence function. I have only year and category fields. I would appreciate any suggestion/help to acheive the below expected output in Power BI. Please let me know if you need any additional information. 

 Output table 

	2018	2019	2020
Category 1	11	4	0
Category 2	9	6	0

Calculations

Category 1 - 2018 = rolling sum of all years  - sum of products in category 1 for 2018

                               =  ((2018 Products) + (2018 + 2019 products) + (2018 + 2019 + 2020 Products) ) - 2018 produts

                               =  ( 2 + (2 + 7) + ( 2+ 7 + 4)) - 2 = 11

Category 1 - 2019 = rolling sum of all years  - sum of products in category 1 for 2019

                               =  ((2018 Products) + (2018 + 2019 products) + (2018 + 2019 + 2020 Products) ) - (2018 + 2019                                                                                                                                                                                        Produts) 

                               =  ( 2 + (2 + 7) + ( 2+ 7 + 4)) - (2 + 7) = 4

similary for all other years and categorys 

Input data

Product	Category	Year
Product 1	Category 1	2018
Product 2	Category 1	2018
Product 3	Category 2	2018
Product 4	Category 2	2018
Product 5	Category 2	2018
Product 6	Category 1	2019
Product 7	Category 1	2019
Product 8	Category 1	2019
Product 9	Category 1	2019
Product 10	Category 1	2019
Product 11	Category 1	2019
Product 12	Category 1	2019
Product 13	Category 2	2019
Product 14	Category 2	2019
Product 15	Category 2	2019
Product 16	Category 1	2020
Product 17	Category 1	2020
Product 18	Category 1	2020
Product 19	Category 1	2020
Product 20	Category 2	2020
Product 21	Category 2	2020
Product 22	Category 2	2020
Product 23	Category 2	2020
Product 24	Category 2	2020
Product 25	Category 2	2020

Thank you

[v-jiascu-msft]

Hi @manojsv16,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[manojsv16]

Thanks a lot Dale!!

[v-jiascu-msft · Accepted solution]

Hi @manojsv16,

I have to say your equation is misleading. Please download the demo in the attachment. 

Measure =
VAR maxYear =
    CALCULATE ( MAX ( Table1[Year] ), ALL ( Table1[Year] ) )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            ALL ( Table1[Year] ),
            'Table1'[Year] > MIN ( 'Table1'[Year] )
                && 'Table1'[Year] <= maxYear
        )
    )

Best Regards,
Dale

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[manojsv16]

Hi Ashish,

I appreciate for providing solution. I would like to let you know that i dont have a date column in the input table. Is there a way to derive the output table without date field. 

Thank you in advance!!

[Ashish_Mathur]

You are welcome.  I have created a Date column from your Year column and then used the DAX Date Intelligence functions.

[manojsv16]

Hi Ashish,

I am sorry for bugging you again 

In my actual data, I can't manipulate the input table by adding date column. I would like to check with you whether we can create a measure without date column in the input data. I have only year field.

Thank you in advance!!

[Ashish_Mathur]

No Problem.  Someone else will help you with this.