Need help with a Power BI visual and DAX measures

[FrankWe]

Hello all,

I have a problem with a visual I need for our business. I would like to create a Stacked Bar Chart with line. On the X-axis I need the year and on the Y-axis I would like to show the sum of field "Volume Year0" belonging to the year (date field) and on the same bar (stacked on top) the sum of the field "Volume Year1" of the previous year. As an example for year 2023 the stacked bar should show the following values:

- Volume Year0 => 550 (400 + 150)
- Volume Year1 => 650 (200 + 450)

Now it is getting complicated. The data in the visual must be filterd to show only the years 2023 und 2024. But this also means that the bar for year 2023 must also show the sum of field "Volume Year1" from 2022.  The item in field "Opp" (Opportunity) is only once in the dataset as the Opportunity will be inserted in the dataset with the creation date (listed in field "Date"). 

I hope that my description is understandable. 

Here you can find the Power BI example file: Power BI Example file 

I "played around" with DAX measures using the "ALL" and "REMOVEFILTERS" functions but without any success yet. I get it working with a visual calculation but then also the year 2022 will be shown in the visual what is not wanted. Only the years 2023 and 2024 must be visable in the stacked bar chart. 

Hopefully sombody here can give the right hint how to do that. Many thanks in advance.

Frank

[Anonymous · Accepted solution]

Hi, @FrankWe 

Thanks for @johnt75's reply. You can try this measure to solve your need.

PreviousYear0 = 
VAR _CurrentYear = YEAR(MAX('FCT_Opportunities'[Date]))
VAR _PreviousYear = _CurrentYear - 1
VAR _result = 
    CALCULATE(
        SUM('FCT_Opportunities'[VolYear1]),
        FILTER(
            ALL('FCT_Opportunities'),
            YEAR('FCT_Opportunities'[Date]) = _PreviousYear
        )
    )
RETURN
_result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[FrankWe]

Spoiler

Hi John and Yang,,

many thanks for your replies. 

I will try both of your solutions and let you know if they are working.

Frank

[Anonymous · Accepted solution]

Hi, @FrankWe 

Thanks for @johnt75's reply. You can try this measure to solve your need.

PreviousYear0 = 
VAR _CurrentYear = YEAR(MAX('FCT_Opportunities'[Date]))
VAR _PreviousYear = _CurrentYear - 1
VAR _result = 
    CALCULATE(
        SUM('FCT_Opportunities'[VolYear1]),
        FILTER(
            ALL('FCT_Opportunities'),
            YEAR('FCT_Opportunities'[Date]) = _PreviousYear
        )
    )
RETURN
_result

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[johnt75]

You can create a couple of measures like

Vol Year 0 = SUM( 'FCT_Opportunities'[VolYear0] )

Vol Year 1 = CALCULATE( SUM( FCT_Opportunities[VolYear1] ), DATEADD( 'Calendar'[Date], -1, YEAR ) )

[FrankWe]

Hello JohnT75,

your hint seems to be the right direction. But now I am struggeling when I try to add additional filters to the measure. I will add this scenario to my example and upload the PIBX later this day to show you what I mean. 

As soon as I add filters to the calculation I do not get any values back.

As an example:

VAR Volume = CALCULATE(

   SUM(FCT_Opportunities[VolYear0]),

      DATEADD( 'Calendar'[Date], -1, YEAR ),

      FILTER(

        FCT_Opportunities,

        FCT_Opportunities[Materialized] = "Open"

    )

)

This will not give me back any values. 😞

[johnt75 · Accepted solution]

Don't filter entire tables, just filter the columns you need. Try

VAR Volume =
CALCULATE (
    SUM ( FCT_Opportunities[VolYear0] ),
    DATEADD ( 'Calendar'[Date], -1, YEAR ),
    FCT_Opportunities[Materialized] = "Open"
)

[FrankWe]

Hi JohnT75,

your solution is working. 👍 Many thanks for that 😊