Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Formatting dashboard with multiple filters

Hello all,  I need your help formatting the dashboard specific way that I'm struggling with. Below are 2 tables that I'm using    Table 1 Site Date Type Sales 2 10/1/2024 Coke 50 1...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi  Anonymous ,

     

    You can try the following dax:

    Bulk =
VAR _7day =
    TODAY () - 7
VAR _30day =
    TODAY () - 30
VAR _365day =
    TODAY () - 365
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Value] ) = "7Day",
            SUMX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Site] = MAX ( 'Table'[Site] )
                        && 'Table1'[Date] > _7day
                        && 'Table1'[Type] = "Apple"
                        && 'Table1'[Bulk] <> "False"
                ),
                [Sales]
            ),
        MAX ( 'Table'[Value] ) = "30Day",
            SUMX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Site] = MAX ( 'Table'[Site] )
                        && 'Table1'[Date] > _30day
                        && 'Table1'[Type] = "Apple"
                        && 'Table1'[Bulk] <> "False"
                ),
                [Sales]
            ),
        MAX ( 'Table'[Value] ) = "365Day",
            SUMX (
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Site] = MAX ( 'Table'[Site] )
                        && 'Table1'[Date] > _365day
                        && 'Table1'[Type] = "Apple"
                        && 'Table1'[Bulk] <> "False"
                ),
                [Sales]
            )
    )

    Bulk_Goal =
VAR _7day =
    TODAY () - 7
VAR _30day =
    TODAY () - 30
VAR _365day =
    TODAY () - 365
VAR _switch =
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Value] ) = "7Day", 7,
        MAX ( 'Table'[Value] ) = "30Day", 30,
        MAX ( 'Table'[Value] ) = "365Day", 365
    )
RETURN
    IF (
        MAX ( 'Table'[Site] ) = 1,
        _switch,
        SUMX (
            FILTER ( ALL ( 'Table2' ), 'Table2'[Site] = MAX ( 'Table'[Site] ) ),
            [Bulk_goal]
        ) * _switch
    )

    Bulk_Diff =
[Bulk] - [Bulk_Goal]

     

     

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi  Anonymous ,

 

When Site=1,Date=2024.9.30 there is no value in 7Days because it is less than Today()-7, I change the date of the data

You can use the CROSSJOIN() function to create a new table by joining [Site] of Table1 with “7Day”, “30Day”, “365Day”. Join to create a new table

 

Here are the steps you can follow：

1. Create calculated table.

Table =
var _table1=
{"7Day","30Day","365Day"}
var _table2=
DISTINCT('Table1'[Site])
RETURN
CROSSJOIN(
    _table2,_table1)

2. Create measure.

Coke =
var _7day=TODAY()-7
var _30day=TODAY()-30
var _365day=TODAY()-365
return
SWITCH(
    TRUE(),
    MAX('Table'[Value])="7Day",
    SUMX(
        FILTER(ALL('Table1'),
        'Table1'[Site]=MAX('Table'[Site])&&'Table1'[Date]>_7day&&'Table1'[Type]="Coke"),[Sales]),
     MAX('Table'[Value])="30Day",
    SUMX(
        FILTER(ALL('Table1'),
        'Table1'[Site]=MAX('Table'[Site])&&'Table1'[Date]>_30day&&'Table1'[Type]="Coke"),[Sales]),  
    MAX('Table'[Value])="365Day",
    SUMX(
        FILTER(ALL('Table1'),
        'Table1'[Site]=MAX('Table'[Site])&&'Table1'[Date]>_365day&&'Table1'[Type]="Coke"),[Sales]))
Coke_Goal =
var _switch=
SWITCH(
    TRUE(),
    MAX('Table'[Value])="7Day",7,
     MAX('Table'[Value])="30Day",30,  
    MAX('Table'[Value])="365Day",365)
return
IF(
    MAX('Table'[Site])=1,_switch
,
SUMX(
    FILTER(ALL('Table2'),'Table2'[Site]=MAX('Table'[Site])),[Coke_goal]) * _switch)
Coke_Diff =
 [Coke] - [Coke_Goal]

3. 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