Forum Discussion

manojsv16's avatar
manojsv16
Icon for Helper II rankHelper II
7 years ago
Solved

DAX Measure to create burndown table

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 

 201820192020
Category 11140
Category 2960

 

 

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

ProductCategoryYear
Product 1Category 12018
Product 2Category 12018
Product 3Category 22018
Product 4Category 22018
Product 5Category 22018
Product 6Category 12019
Product 7Category 12019
Product 8Category 12019
Product 9Category 12019
Product 10Category 12019
Product 11Category 12019
Product 12Category 12019
Product 13Category 22019
Product 14Category 22019
Product 15Category 22019
Product 16Category 12020
Product 17Category 12020
Product 18Category 12020
Product 19Category 12020
Product 20Category 22020
Product 21Category 22020
Product 22Category 22020
Product 23Category 22020
Product 24Category 22020
Product 25Category 22020

 

 

Thank you

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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
        )
    )

    DAX-Measure-to-create-burndown-table

    Best Regards,
    Dale

8 Replies

    • manojsv16's avatar
      manojsv16
      Icon for Helper II rankHelper II
      7 years ago

      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's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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
        )
    )

    DAX-Measure-to-create-burndown-table

    Best Regards,
    Dale