How to filtered out to get cumulative based on the filters inside DAX

[Mihai_Iso]

Hello,

I need to solve the issue from below :

INPUT

-Dates and Week number “Week no”

-budget

-actual

-Table

-a column Group (main, second, third, fourth)

-another column Status (A, B, C), all Status all assigned to all Group items.

How to get:

-for budget cumulative, actual cumulative, forecast cumulative, we need filtered out: “fourth” Group, and “third” Group without “A” and “B” Status.

Forecast cumulative must be linear distributed, and having an imposed date (16/04/2023, or week 15) 

In these above cases,

how to calculate budget, actual, forecast cumulative, based on “Dates”  ,  or by “Week no”  ,

 filtered out “fourth” Group, and “third” Group without “A” and “B” Status.

Some formulas for filtering ,maybe will help you:

FILTER(

        'Table',

        'Table'[Group] <> "fourth" &&

        NOT('Table'[Group] = "third" &&

            ('Table'[Status] = "A" || 'Table'[Status] = "B")) &&

        'Table'[Date] <= MAX('Table'[Date])

    )

and another one could be:

FILTER('Table','Table'[Group] <> "fourth" &&

                                    NOT('Table'[Group] = "third" && 'Table'[Status] IN {"A", "B"}) &&

                                    'Table'[Date] <= MAX('Table'[Date]))

                   )

Power bi 

Excel file 

Thank you.

[Mihai_Iso]

Week with Actual = IF('Table 5'[Date] <= [Last Week Actual], 'Table 5'[Date], BLANK())
).
Last Week Actual = MAXX(FILTER('Table 5', 'Table 5'[Has Actual] = 1), 'Table 5'[Date])
Has Actual = IF('Table 5'[actual] > 0, 1, 0)

All these alre calculated columns from Table 5 !

Measures:

actual cumulative (dates)33 =

IF (

    NOT ISBLANK ( MAX ( 'Table 5'[actual] ) ),

    CALCULATE (

        SUM ( 'Table 5'[actual] ),

        FILTER ( ALLSELECTED ( 'Table 5' ), 'Table 5'[Date] <= MAX ( 'Table 5'[Date] ) )

    )

)

budget cumulative (dates) 33 =

CALCULATE (

    SUM ( 'Table 5'[budget] ),

    FILTER ( ALLSELECTED ( 'Table 5' ), 'Table 5'[Date] <= MAX ( 'Table 5'[Date] ) )

)

and

Table 5 =

CALCULATETABLE (

    'Table',

    FILTER('Table','Table'[Group] <> "fourth" &&

                                    NOT('Table'[Group] = "third" && 'Table'[Status] IN {"A", "B"}) ))

[Mihai_Iso]

Hi,

I think it is better to use CALCULATETABLE to get the desired table with multi-criteria filter,but still do not know how to get forecast cumulative linear distribution for imposed date 16/04/2023...does not work for Table 5 ,it works for Table (in general,with no filters)

Table 5 =

CALCULATETABLE (

    'Table',

    FILTER('Table','Table'[Group] <> "fourth" &&

                                    NOT('Table'[Group] = "third" && 'Table'[Status] IN {"A", "B"}) &&

                                    'Table'[Date] <= MAX('Table'[Date]))

                   )

and maybe we can customize this

Forecastcumulative linear  end date_Imposed 33 =

VAR _budgetall =

    CALCULATE ( SUM ( 'Table 5'[budget] ), REMOVEFILTERS ( 'Table 5' ) )

VAR _actualall =

    CALCULATE ( SUM ( 'Table 5'[actual] ), REMOVEFILTERS ( 'Table 5' ))

VAR _diffamount = _budgetall - _actualall

VAR _lastbudgetdate =

    CALCULATE ( MAX ( 'Table 5'[Date] ), REMOVEFILTERS ( 'Table 5' ) )

VAR _lastactualdate =

    CALCULATE ( MAX ( 'Table 5'[Date] ), REMOVEFILTERS ( 'Table 5' ))

VAR  _diffdates_A =

    DATEDIFF ( _lastactualdate, _lastbudgetdate, DAY )

VAR _ForecEndDate = DATE(2023,04,16)

VAR _diffdates =

    DATEDIFF ( _lastactualdate, _ForecEndDate, DAY )

VAR _forecastcumulate =

    SWITCH (

        TRUE (),

        MAX ( 'Table 5'[Date]) = _lastactualdate, _actualall,

        MAX ( 'Forecast cut-off'[Date] ) > _lastactualdate

            && MAX ( 'Forecast cut-off'[Date] ) <= _ForecEndDate,

            _actualall

                + (

                    DIVIDE ( _diffamount, _diffdates)

                        * DATEDIFF ( _lastactualdate, MAX ( 'Forecast cut-off'[Date] ), DAY )

                )

    )

RETURN

_forecastcumulate

What do you think?

[Mihai_Iso · Accepted solution]

Hi,

here is the issue:

"

VAR _lastbudgetdate =
CALCULATE ( MAX ( 'Table 5'[Date] ), REMOVEFILTERS ( 'Table 5' ) )
VAR _lastactualdate =
CALCULATE ( MAX ( 'Table 5'[Date] ), REMOVEFILTERS ( 'Table 5' ))

"

if you could find the correct "lastactualdate" from the "Table 5", this must be different of "lastbudgetdate" (now both have the same formula...).

"'Table 5'[Date]" from _lastactualdate must be replaced with the correct one

(tips:

Week with  Actual = IF('Table 5'[Date] <= [Last Week Actual], 'Table 5'[Date], BLANK())

).

Last Week Actual = MAXX(FILTER('Table 5', 'Table 5'[Has Actual] = 1), 'Table 5'[Date])

Has Actual = IF('Table 5'[actual] > 0, 1, 0)

Good luck!

[lbendlin]

actual cumulative (dates) = 
var fc = MAX ( 'Forecast cut-off'[Date] )
return IF (
    NOT ISBLANK ( MAX ( 'Table'[actual] ) ),
    CALCULATE (
        SUM ( 'Table'[actual] ),
        'Table'[Date] <= fc ,
        'Table'[Group]<>"fourth",
        not ('Table'[Group]="third" && 'Table'[Status] IN {"A", "B"})
    ) 
)

[Mihai_Iso]

Hi,

thank you for the "actual cumulative"!

In the same approach,I have done for "budget cumultive", but I need to stop after the first maximum value 33.

budget cumulative =

var fc = MAX ( 'Forecast cut-off'[Date] )

return IF (

    NOT ISBLANK ( MAX ( 'Table'[budget] ) ),

    CALCULATE (

        SUM ( 'Table'[budget] ),

        'Table'[Date] <= fc ,

        'Table'[Group]<>"fourth",

        not ('Table'[Group]="third" && 'Table'[Status] IN {"A", "B"})

    )

)

And I would like to have the forecast cumulative,linear distributed, with the date imposed 16/04/2023,as shown in excel file.

Thank you .