Help with data preparation for Waterfall Report

[PS_78]

Hi Team - I am working on generating waterfall charts in my Power BI report. I have a dataset with "Raw Data" and I need to create a calculated table, "Waterfall" with columns "Categories" and "Value" to use them in the charts. I can somehow manage it in "Power Query" but any guidance on how I can generate this table in DAX that would be great. Attaching herewith the sample file I generated.

Waterfall Sample.pbix

Thanks,

Phani

[v-tangjie-msft · Accepted solution]

Hi @PS_78 ,

You can use the following DAX to create the Waterfall table.

Waterfall table = DATATABLE (
    "Category", STRING,
    "Value", INTEGER,
    "SORT", INTEGER,
    {
        {"Starting Value_Year", 2, 1},
        {"New_Year", 9, 2},
        {"Closed_Year", -3, 3},
        {"Starting Value_Qrtr", 8, 1},
        {"New_Qrtr", 1, 2},
        {"Closed_Qrtr", -1, 3}
    }
)

If I have misunderstood your needs, please provide your expected result with backend logic and special examples.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[v-tangjie-msft · Accepted solution]

Hi @PS_78 ,

You can use the following DAX to create the Waterfall table.

Waterfall table = DATATABLE (
    "Category", STRING,
    "Value", INTEGER,
    "SORT", INTEGER,
    {
        {"Starting Value_Year", 2, 1},
        {"New_Year", 9, 2},
        {"Closed_Year", -3, 3},
        {"Starting Value_Qrtr", 8, 1},
        {"New_Qrtr", 1, 2},
        {"Closed_Qrtr", -1, 3}
    }
)

If I have misunderstood your needs, please provide your expected result with backend logic and special examples.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[PS_78]

Hello @v-tangjie-msft - Sorry for responding late. Thanks for your response. That looks like a good idea. However, you are hardcoding the values. I am okay to hardcode category and SORT but not the value. I need to consolidate the "Value" from "Raw Data" table.

Thanks,

Phani

[v-tangjie-msft]

Hi @PS_78 ,

Can you explain in detail with an example how to get the values of table 2 from table 1? What is their logic?

Best Regards,

Neeko Tang

[PS_78]

Hello @v-tangjie-msft - I'll try to elaborate the same below. Please check.

1. Starting Value_Year --> All the issues created in previous year(S) (not current year) but open as of 1st Jan of current year

2. New_Year --> Issues created in current year

3. Closed_Year --> Issues closed in current year (it doesn't matter when it was created)

4. Starting Value_Qrtr --> All the issues created in previous quarter(S) (not current year / current quarter) but open as of 1st day of the current year current quarter

2. New_Year --> Issues created in current year current quarter

3. Closed_Year --> Issues closed in current year current quarter (it doesn't matter when it was created)

I hope this gives you an idea.

Thanks,

Phani

[v-tangjie-msft · Accepted solution]

Hi @PS_78 ,

You can create a table.

Waterfall table = DATATABLE (
    "Category", STRING,
    "SORT", INTEGER,
    {
        {"Starting Value_Year", 1},
        {"New_Year",2},
        {"Closed_Year",3},
        {"Starting Value_Qrtr",1},
        {"New_Qrtr",2},
        {"Closed_Qrtr", 3}
    }
)

Then you can create a column.

Value = 
var _new_year=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())))
var _close_year=-CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Closed])=YEAR(TODAY())))
var _start_year_1=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())-1 && [Date Closed]<=DATE(YEAR(TODAY()),1,1)))
var _start_year_2=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())-1 && [Open/Closed]="Open"))
var _new_qrtr=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',QUARTER('Raw Data'[Date Identified])=QUARTER(TODAY())))
var _close_qrtr=-CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',QUARTER('Raw Data'[Date Closed])=QUARTER(TODAY())))
var _start_qrtr=CALCULATE(DISTINCTCOUNT('Raw Data'[Issue #]),FILTER('Raw Data',YEAR('Raw Data'[Date Identified])=YEAR(TODAY())  && [Date Closed]<=DATE(YEAR(TODAY()),QUARTER(TODAY())*3-2,1)))

RETURN SWITCH([Category],
"Starting Value_Year",_start_year_1+_start_year_2,
"New_Year",_new_year,
"Closed_Year",_close_year,
"Starting Value_Qrtr",_start_qrtr,
"New_Qrtr",_new_qrtr,
"Closed_Qrtr",_close_qrtr)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.