Filter measures with two same filters

[fazou]

Hello Guys,

AS you can see in the screenshot i have a bar chart with two different measures SALES_COMPARISON and SALES_REFERENCE
SALES_REFERENCE will be filtered by two filters of REFERENCE_SEASON  and week of Yar as Axe X

SALES_COMPARISON  will be filtered by two filters COMPARISON_SEASON

two filters section have same columns :
PRODUCT_SEASON from SALES table
WEEK of Year from DATE_DIMENSION

what i want to do is to choose values from REFERENCE SEASON and compare them to values from filter section COMPARISON SEASON as shown below,but it didn't work

 any one have an ida how to achieve it please ?
do i need to duplicate SALES and DATE tables if it is the soliution would be so much to duplicate a sales table ?

thank you for your help 🙂 

https://drive.google.com/file/d/1VNvq-6D-9T9ft-OF1aU58Qm9LNpNA27A/view?usp=sharing 

[Anonymous]

Hi @fazou ,

1. Please create two more tables, the same SALES and DATE_DIMENSION tables, replacing the fields and slicers used for one of the measures with the fields in the new tables.

2. Modify the original formula without ignoring other filters:

SALES_REFERENCE = SUM(SALES[NET_SALES])
SALES_COMPARISON = SUM(NEW_SALES[NET_SALES])

3. Create a new date table, and the relationship is as follows:

4.Use the field in the new date table as the x-axis. Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[fazou]

@Anonymous  Any idea on how to calculate the runing total ? for exp if you select week 44 it SHOW NETSALES OF WEEK 44 and all previous weeks in line chart ? thank you

i tried this 

SALES_REFERENCE = CALCULATE(SUM('SALES'[NET_SALES]),FILTER(ALLSELECTED('DIM_DATE'),DIM_DATE[WEEKOFYEAR]<=MAX(DIM_DATE[WEEKOFYEAR])),DIM_DATE[WeekOfYear]=SELECTEDVALUE(DIM_DATE[WeekOfYear])

)

[Anonymous · Accepted solution]

Hi @fazou ,

You can create another date table as a slicer, don't create a relationship with the other table, and create a measure

and cancel the interaction with other slicers.

Measure = IF(MAX('DATE_DIMENSION'[Week of Year])<=SELECTEDVALUE('Date2'[WeekOfYear]),
CALCULATE(SUM('SALES'[NET_SALES]),FILTER(ALLSELECTED('DATE_DIMENSION'),'DATE_DIMENSION'[Week of Year]<=MAX('DATE_DIMENSION'[Week of Year]))))

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[fazou]

@Anonymous  thank you for help,can you explain more your solution please ? or put the pbix if it's possible, thank you 🙂