Custom waterfall chart include previous months

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.