Sum excluding slicer filters but including other filters

[Anonymous]

I'm at a loss and I need some help. I'm trying to summarize values based on the last 4 weeks from a selected slicer date. In essence, if i select 3/5 in a slicer, i want to see the total sales from 3/5, 2/26, 2/19, and 2/12. If i don't have anything in my visual selected i get the proper amount

However when i select 3/5 in my slicer, my data only shows the value from 3/5

Here is the equation i'm using for the test sum. Right now the FW weeks old values are hard coded but I'd theoretically like them to depend on the selection of the date. So if they select 3/5 it should show the 4 weeks prior to 3/5. If they select 4/16, it should show 4/16, 4/9, 4/2, and 3/26. I have some measures i'd throw in the filters at the top instead of the numbers. I'll also post those below.

test sum =

Var Filtered = filter(View_LU_FiscalCalendar,View_LU_FiscalCalendar[FW Weeks Old] <= 27)

Var Filtered2 = filter(View_LU_FiscalCalendar,View_LU_FiscalCalendar[FW Weeks Old] >= 24)

Return

calculate(FT_MIS_Data[Total Value],all(FT_MIS_Data[Date]), filtered,Filtered2)

In a perfect world, my equation would look something like this so that it would be based on whatever value is selected in the slicer.

Measures for Latest dates:

Selected CW Latest Date = selectedvalue(View_LU_FiscalCalendar[FW Weeks Old])

Selected CW Earliest Date = selectedvalue(View_LU_FiscalCalendar[FW Weeks Old])+3

test sum Ideal=

Var Filtered = filter(View_LU_FiscalCalendar,View_LU_FiscalCalendar[FW Weeks Old] <= FT_MIS_Data[Selected CW Earliest Date])

Var Filtered2 = filter(View_LU_FiscalCalendar,View_LU_FiscalCalendar[FW Weeks Old] >= FT_MIS_Data[Selected CW Latest Date])

Return

calculate(FT_MIS_Data[Total Value],all(FT_MIS_Data[Date]), filtered,Filtered2)

My FW weeks old column is in a calendar table and my values are in my FT_MIS_Data table. Below is the diagram:

Thank you any and all suggestions. I appreciate your time. 

Cheers,
Nick

[Anonymous · Accepted solution]

Hi Dave,

Thank you for the response but I figured out how to do it differently using the values I had late last night. I just learned of dummy tables and thought that might help. I created a Dummy date table that had my end of week date as well as the fiscal weeks old value i needed (added a second column with a lookup). I then tied that to my data table and changed the slicer to use that dummy table date. I then made the following equation to calculate what i needed. The first calculate is really what's needed. The if statement just makes it so the reports aren't blank if no date is selected.


Equation:

Last4WeeksValuesNotBlank = var mySlicerDate = SELECTEDVALUE(View_LU_FiscalCalendar[FW Weeks Old])

Return

if(

isfiltered(View_LU_FiscalCalendar[FW End Date]),

Calculate(FT_MIS_Data[Total Value],all(View_LU_FiscalCalendar),

filter(

'Dummy Date Table',

'Dummy Date Table'[FW Old] < mySlicerDate + 4 &&

'Dummy Date Table'[FW Old] >= mySlicerDate))

,

Calculate(FT_MIS_Data[Total Value],all(View_LU_FiscalCalendar),

filter(

'Dummy Date Table',

'Dummy Date Table'[FW Old] <5 &&

'Dummy Date Table'[FW Old] >= 0))

)

For anyone else wondering how to do the same thing, here are the resources i used:

1. https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-...
2. https://dax.tips/2020/02/05/overriding-slicers/
3. https://www.antmanbi.com/post/show-items-not-selected-in-a-slicer

[davehus]

Hi @Anonymous ,

https://drive.google.com/file/d/1Rka06mld17hazUg94Y6wr9QnGfVvO19S/view?usp=sharing

Please see the attached PBIX, In the date table, I've added a couple two columns 1 that returns the last week date and then a conditional column called IsLastDay. (Power Query). Add these to your date table if you can. Then drag the full date key into a slicer and filter the visual using IsLastDay to Y.

You can then use the measure called L4 weeks to give you the last 4 weeks using the DATESINPERIOD formula.

Let me know if you need anymore help on this.

D

Did I help you today? Please accept my solution and hit the Kudos button.