cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jahnabiAP
Frequent Visitor

Want to Show YTD vs YTD Previous year in Waterfall

Hi,
I am trying to use Waterfall visual for showing YTD VS YTD Previous Year, where the right total column will be Sales YTD FY20  the left total column will be YTD sales(for same period) for FY 2019 . I have 2 measures for both YTD. How to show in a waterfall.
The breakdowns will be various activities

Waterfal.PNG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
crisc Helper I
Helper I

Re: Want to Show YTD vs YTD Previous year in Waterfall

Hi @jahnabiAP ,

 

this is what our chart looks like:Waterfall created form stacked columns.png

 

Even though the left bar does not show the previous year and the right one the actual (its a leadfunnle), this will work the same way for any deviation.

 

The red colums are simply a SUM Measure.

The measure for the blanks looks like this:

DAQBlank = 

VAR __FilAbgAll = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbgeschlossenALL")
VAR __FilUngue = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketUngueltig")
VAR __FilGeloe = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketGeloescht")
VAR __FilErsch = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketErschienen")
VAR __FilAbbOPU = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchohneSchnupper-/PU")

RETURN
SWITCH(
    VALUES(DAQ_FactSamsEnde[Attribut]);
    "DAQTicketUngueltig";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbgAll
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilUngue
        );
    "DAQTicketGeloescht";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbgAll
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilUngue
        )
        -
        CALCULATE(
        SUM(DAQ_FactSamsEnde[Wert]);
        __FilGeloe
        );
    "DAQLeadsohneTermin";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketGueltig")
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQLeadsohneTermin")
        );
    "DAQTicketNoShow";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketLeads_mit_Termin")
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketNoShow")
        );
    "DAQTicketAbbruchohneSchnupper-/PU";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilErsch
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbbOPU
        );
    "DAQTicketAbbruchmitSchnupper-/PU";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilErsch
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbbOPU
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchmitSchnupper-/PU")
        );
    0
)

 I am sure there is a much simplier way to fill the blanks, but that was what we came up with in the end...

View solution in original post

6 REPLIES 6
Microsoft V-lianl-msft
Microsoft

Re: Want to Show YTD vs YTD Previous year in Waterfall

 
You can use the breakdown feature to solve this. 
 
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
jahnabiAP
Frequent Visitor

Re: Want to Show YTD vs YTD Previous year in Waterfall

@V-lianl-msft  Thanks for you quick response.
But I am already using activities for breakdown.
So its like YTD changes over the activities for FY 20 VS FY 19.
I am using Fiscal year for categories, activities for breakdown, and Sales for Y axis values.
I would like tpo use YTD and Last year YTD in place of Y axis values.

crisc Helper I
Helper I

Re: Want to Show YTD vs YTD Previous year in Waterfall

Hi @jahnabiAP ,

 

we had a similar situation in a report. At the end we solved this by:

 

  1.  Creating a custom table with our categories for the breakdown & a column to order them
  2. Creating a measure to calculate the deviation from each breakdown
  3. Creating a measure for each blank space "below" the deviation
  4. Use a stacked column chart

 

The measures were similar to this:

DEVIATION_BREAKDOWN =
 SWITCH(
  TRUE();
  BREAKDOWN1;<Some calculation/Measure>;
  BREAKDOWN2;<Some calculation/Measure> 
 )

BLANKS =
 SWITCH(
  TRUE();
  BREAKDOWN1;YTD PREV YEAR +
   CALCULATE(
    <DEVIATION_BREAKDOWN>;
    FILTER(
     ALL(BREAKDOWN_TABLE);
     BREAKDOWN == "BREAKDOWN1"
    )
   );
  BREAKDOWN2;YTD PREV YEAR +
   CALCULATE(
    <DEVIATION_BREAKDOWN>;
    FILTER(
     ALL(BREAKDOWN_TABLE);
     BREAKDOWN == "BREAKDOWN1" || BREAKDOWN == "BREAKDOWN2"
     )
    )
   )
  )

(Code written from head & not checked!)

 

Basically you need to add up the YTD Last Year and all the deviations for each breakdown(s) on and before the breakdown you are looking at.

We know that maintenance for this is pretty high, but it was the only way we could get it to work...

 

Chris

jahnabiAP
Frequent Visitor

Re: Want to Show YTD vs YTD Previous year in Waterfall

Thanks for the response @crisc .
But I want to show the YTD difference in a waterfall chart.
Can you show me how your chart will look like?

crisc Helper I
Helper I

Re: Want to Show YTD vs YTD Previous year in Waterfall

Hi @jahnabiAP ,

 

this is what our chart looks like:Waterfall created form stacked columns.png

 

Even though the left bar does not show the previous year and the right one the actual (its a leadfunnle), this will work the same way for any deviation.

 

The red colums are simply a SUM Measure.

The measure for the blanks looks like this:

DAQBlank = 

VAR __FilAbgAll = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbgeschlossenALL")
VAR __FilUngue = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketUngueltig")
VAR __FilGeloe = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketGeloescht")
VAR __FilErsch = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketErschienen")
VAR __FilAbbOPU = FILTER(ALLSELECTED(DAQ_FactSamsEnde);DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchohneSchnupper-/PU")

RETURN
SWITCH(
    VALUES(DAQ_FactSamsEnde[Attribut]);
    "DAQTicketUngueltig";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbgAll
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilUngue
        );
    "DAQTicketGeloescht";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbgAll
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilUngue
        )
        -
        CALCULATE(
        SUM(DAQ_FactSamsEnde[Wert]);
        __FilGeloe
        );
    "DAQLeadsohneTermin";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketGueltig")
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQLeadsohneTermin")
        );
    "DAQTicketNoShow";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketLeads_mit_Termin")
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketNoShow")
        );
    "DAQTicketAbbruchohneSchnupper-/PU";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilErsch
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbbOPU
        );
    "DAQTicketAbbruchmitSchnupper-/PU";
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilErsch
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            __FilAbbOPU
        )
        -
        CALCULATE(
            SUM(DAQ_FactSamsEnde[Wert]);
            FILTER(
                ALLSELECTED(DAQ_FactSamsEnde);
                DAQ_FactSamsEnde[Attribut]="DAQTicketAbbruchmitSchnupper-/PU")
        );
    0
)

 I am sure there is a much simplier way to fill the blanks, but that was what we came up with in the end...

View solution in original post

jahnabiAP
Frequent Visitor

Re: Want to Show YTD vs YTD Previous year in Waterfall

Thanks very much for your solution.
Its a complex one though. 😛

I will try to use this approach.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors