Forum Discussion

hassanemam's avatar
hassanemam
Regular Visitor
6 years ago

DAX: Using Date Measure as Filter in another measure does not return expected value

I am creating a Power BI report that need to do the following

 

1 extract the latest date where the sum of values EV is greater than Cummalitive PV

so I created a measure for **bleep** PV as follows

 

 

 

PV = CALCULATE(SUM(Planned[Value]),
Filter(
ALLSELECTED(Planned), 
Planned[Dattes]<= MAX(Planned[Dattes])))

 

 

 

 

And the Latest date Measure as

 

 

 

EqDates = CALCULATE(LASTNONBLANK(Planned[Dattes], Planned[Dattes]), FILTER(ALL(Planned), [**bleep** PV] <= SUM(Activities[EV])))

 

 

 

 

2 Now I want to create another measure that calculate the cumulative cost value up to the EqDates calculated in the previous Step

 

I created the following measure 

 

 

PV1 = CALCULATE(SUM(Planned[Cost]), FILTER(ALLSELECTED(Planned), Planned[Dattes] <= MAX(Planned[Dattes]) && DATEVALUE([EqDates]) >= Planned[Dattes] ))

 

 

 

 

But it does not return the expected value. Basically the EqDates has a value of 24 March 2020, the PV1 cumulative value stops incrementing on 26 March 2019 (around a year earlier). it seems that filtering using EqDates in PV1 does not work.

 

Any advice on what I am doing wrong?

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     hassanemam 

     

    Try replace :

    EqDates = CALCULATE(LASTNONBLANK(Planned[Dattes], Planned[Dattes]), FILTER(ALL(Planned), [**bleep** PV] <= SUM(Activities[EV])))

    with:

    EqDates = CALCULATE(LASTNONBLANK(Planned[Dattes], Planned[Dattes]), FILTER(ALL(Planned), [**bleep** PV] <= SUMX(ALL(Activities),[EV])))

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.