Formatting dashboard with multiple filters

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

Anonymous 

Hello,

First of all, thank you for taking the time to post on my board and assisting me with formatting my dashboard. Your solution worked perfect!! 

There is one additional change I wanted to make and see if you can help me figure this out.

Table 1

Here I have addeda column "Bulk" where it is only comparable to the Type "Apple".  I want to make 2 changes to what you sent me. 
1) I need to recalculate the Apple total with excluding where Bulk is "False"
2) I need to add another columns for only the Bulk Sales

Please see below for the final result.