Date filter based on slicer date filter

[TristanKuesters]

Hello everybody,

it is time for a new challenge.

The scenario:

-----

I wanted to know the sales amount from our collegues based on a specific date range.

The date range will be defined by a Sprint (Scrum methodology) of 3 weeks.

So filter on Sprint 98 filter the date to 06th march to 26th march.

The hours spend for a specific collegues ales tracked in a fact table. A simpel sum(table[hours]) get what I need for the above mentoined three weeks. Lets say 80 hours.

On the other hand I have a sales amount of 1000 € with a posting date of 31th of march.

-----

Soo.. what I think about is the following:

Lets get the total hours for the complete march for this collegue, lets say 100 hours, divide them then against the 1000€ - and then we have the sales price per hours. Last step is to multiply the 80 hours for the above mentoined sprint with the sales price of 10€. Result will be 800€ sales amount for this collegue.

---

But of course my challenge is to ignore the initial date filter, and set for a measure a new date filter for march. How can I do this?

And 2nd step: What, wenn the sprint 99 is from 27th march to 16th of april? Now I have a bigger datefilter about two month..

So, any comment or question are welcome!

Best regards, Tristan

[Anonymous]

Hi @TristanKuesters,

>>But of course my challenge is to ignore the initial date filter, and set for a measure a new date filter for march.
It is simply to use "all/allselected" function to ignore the default date filter.

About the new data filter, you can add a slicer on data column, then combination use  "allselected" with "firstdate/lastdate" to get the min and max date.

Sample measure:

Calculate based on slicer = 
var SelectDateMin=FIRSTDATE(ALLSELECTED('Table'[Date]))
var SelectDateMax=LASTDATE(ALLSELECTED('Table'[Date]))
return
CALCULATE(formula, FILTER(ALL('Table'),[Date]>=SelectDateMin&&[Date]<=SelectDateMax))

Notice: "formula" part means the calculation of your formula.

If above is not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

[TristanKuesters · Accepted solution]

Hello Xiaoxin,

thanks for your answer. Sometime the solution is so simple.

The only adjustment I made to get the complete month is the following:

Calculate based on slicer = 
var SelectDateMin=FIRSTOFMONTH(ALLSELECTED('Table'[Date]))
var SelectDateMax=LASTOFMONTH(ALLSELECTED('Table'[Date]))
return
CALCULATE(formula, FILTER(ALL('Table'),[Date]>=SelectDateMin&&[Date]<=SelectDateMax))