Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with Date filtering on measure

I have a table that has contract data and I need to show Contract YTD info. Here is an example:

 

Contract1 active dates 1/1/2018 - 12/31/2018

Contract2 active dates  4/1/2018 - 5/31/2019

Contract3 active dates  5/1/2018 - 12/31/2018

 

My data is setup so that contract2 would look like this:

 

Date...........Contract...........Amount.............Active
4/1/2018.......contract2.........500....................1
5/1/2018.......contract2.........600....................1
6/1/2018.......contract2.........900....................1
7/1/2018.......contract2.........500....................1
8/1/2018.......contract2.........500....................1
9/1/2018.......contract2.........400....................1
10/1/2018.....contract2.........500....................1
11/1/2018.....contract2.........500....................1
12/1/2018.....contract2.........200....................1
1/1/2019.......contract2.........500....................1
2/1/2019.......contract2.........900....................1
3/1/2019.......contract2.........500....................1
4/1/2019.......contract2.........200....................1
5/1/2019.......contract2.........100....................1

I have another regular daily transaction table (factSales) that is tracking Quantity.

 

What I need to do is figure out the DAX to get the total Quantity for only the contract YTD. Below is true YTD. I need to alter this so that the starting date will always be the first Contract Date for the respective contract. So, I'd want contract1 to start on 1/1/2018, Contract2 to start 4/1/2018, and contract3 to start 5/1/2018. I already have other filtering so it will only return Active contracts. My issue is with limiting the Quantity from Sales to stay within the contract date bounds.

 

YTDTotalInvoiced = CALCULATE(SUM(factSales[Quantity]),filter('Date','Date'[Date]<=today()-1 && 'Date'[Date]>=DATE(YEAR(TODAY()-1),1,1)))
  • Anonymous's avatar
    Anonymous
    7 years ago

    It was not solved in Power BI. I had to change my fact table to include the Sales Quantity to get it to work as desired. Not ideal.

4 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Anonymous

     

    Please check if below measure could help you.

    Measure  =
    CALCULATE (
        SUM ( factSales[Amount] ),
        FILTER (
            ALL ( factSales ),
            factSales[Date] >= MAX ( Contract[active dates] )
                && factSales[Date] <= MAX ( Contract[end dates] )
        )
    )
    

    Regards,

    Cherie

     

  • Anonymous's avatar
    Anonymous
    Not applicable

    Try this.

     

    TOTALYTD(SUM(factSales[Quantity]),'Calendar'[Date],ALL(factSales),"5/31/2019") - for Contract 2.

    Use the same for Contract 1 and 3 (change the year end date), it will work.

    Please let me know if you have any questions.

     

    Regards,

    Pavan Vanguri.

     

     

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Anonymous

     

    Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

     

    Regards,

    Cherie

    • Anonymous's avatar
      Anonymous
      Not applicable

      It was not solved in Power BI. I had to change my fact table to include the Sales Quantity to get it to work as desired. Not ideal.