Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

FILTER DATES

Hi community, 

 

I am trying to filter by multiple date conditions. 

Can you please help me achieving the two conditions that I have specified within the below filters? 

 

Measure = 

CALCULATE(
COUNT(t_Missions[ID Mission]);
FILTER( Date de présentation à l'entité 1 > Beginning of Previous Quarter);
FILTER( Date de présentation à l'entité 1 < = End of The selected month);
)

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    1.

    create a calendar table

    calendar = CALENDARAUTO()

    2.

    create relationship as below

     

    3.

    add [date] from "calendar" table

    Create measures in Sheet13

    Measure = COUNT(Sheet13[id])
or
Measure 2 = CALCULATE(COUNT(Sheet13[id]),ALLSELECTED(Sheet13))

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous  - 

    Another way to state your requirement is: Count Missions for the selected month and the prior months within the current quarter. Is this correct?

     

    You will want to do the following:

    1. Create a Date table that has Month, Quarter attributes, along with others.

    2. Mark the Table as a Date Table

    3. Create a Relationship between your date table and fact table.

    4. Create a Measure like this:

    Measure = 
var selected_month = SELECTEDVALUE('Date'[MonthKey]) //e.g. 201902
var selected_quarter = SELECTEDVALUE('Date'[QuarterKey]) //e.g. 201901 (1st quarter 2019)
return CALCULATE(
    COUNT(t_Missions[ID Mission]);
    'Date'[QuarterKey] = selected_quarter;
    'Date'[MonthKey] <= selected_month
)

    Hope this helps,

    Nathan

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    Without knowing the table structure you have in your data set I can't be more precise than this, but you can use something similar to the following: 

    IF(
    AND(
        Sales[DateKey] > DATE(2018,13,31),
        Sales[DateKey] >= DATE(2019,02,28)),
    1,
    0
)
  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    1.

    create a calendar table

    calendar = CALENDARAUTO()

    2.

    create relationship as below

     

    3.

    add [date] from "calendar" table

    Create measures in Sheet13

    Measure = COUNT(Sheet13[id])
or
Measure 2 = CALCULATE(COUNT(Sheet13[id]),ALLSELECTED(Sheet13))

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.