Forum Discussion

doctorv1979's avatar
doctorv1979
Frequent Visitor
3 years ago

Custom waterfall chart include previous months

 
I need to include prevvious months data as static bars on my chart in addition to the the waterfall chart. Something like this. Is this possible. I need to show End FY22, End Q1, Jauary and February data. I can do it in excel but unable to do this in Power BI. Any tips?

 

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  doctorv1979 ,

     

    Here are the steps you can follow：

    1. Create calculated column.

     

    Month = FORMAT('Table'[Date],"mmmm" )

     

    2. Enter data – create table.

    3. Create calculated table.

     

    Table 2 =
var _table1=
SUMMARIZE('Table','Table'[Month])
return
UNION(
  _table1  ,'Group Table')

     

    4. Create measure.

     

    Measure =
var _today=YEAR(TODAY())
return
SWITCH(
    TRUE(),
    MAX('Table 2'[Month])="End FY22",
    SUMX(
        FILTER(ALL('Table'),
        YEAR('Table'[Date])=2022),[Value]),
        MAX('Table 2'[Month])="End FY23",
    SUMX(
        FILTER(ALL('Table'),
        YEAR('Table'[Date])=2023),[Value]),
    MAX('Table 2'[Month])= "End Q1",
    SUMX(
        FILTER(ALL('Table'),
        YEAR('Table'[Date])=_today&&QUARTER('Table'[Date])=1),[Value])
  ,
    MAX('Table 2'[Month])= "End Q2",
    SUMX(
        FILTER(ALL('Table'),
        YEAR('Table'[Date])=_today&&QUARTER('Table'[Date])=2),[Value]),
    MAX('Table 2'[Month])= "End Q3",
    SUMX(
        FILTER(ALL('Table'),
        YEAR('Table'[Date])=_today&&QUARTER('Table'[Date])=3),[Value]),
    MAX('Table 2'[Month])= "End Q4",
    SUMX(
        FILTER(ALL('Table'),
        YEAR('Table'[Date])=_today&&QUARTER('Table'[Date])=4),[Value]),
    SUMX(
        FILTER(ALL('Table'),
        YEAR('Table'[Date])=_today&&'Table'[Month] in SELECTCOLUMNS('Table 2',"1",[Month])),[Value]))
    Flag =
var _table=
FILTER(
'Table',NOT( 'Table'[Month]) in {"January","February"})
var _table2=
SUMMARIZE(
    _table,[Month])
return
IF(
    MAX('Table 2'[Month]) in _table2,0,1)

     

     

    5. Place [Flag]in Filters, set is=1, apply filter.

    6. Result:

     

    Best Regards,

    Liu Yang

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