Forum Discussion

TristanKuesters's avatar
TristanKuesters
Icon for Resolver I rankResolver I
9 years ago
Solved

Date filter based on slicer date filter

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

 

Need Help
  • TristanKuesters's avatar
    TristanKuesters
    9 years ago

    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))

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      TristanKuesters
      Icon for Resolver I rankResolver I
      9 years ago

      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))