Help with Date filtering on measure

[Anonymous]

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

[v-cherch-msft]

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 · Accepted solution]

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.

[Anonymous]

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]

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