Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

value comparison between two dates

Hello Community,   Hope you are well. I am struggling with dates comparison and I would appreciated if you could help me. I try to build a table that will show the value delta of two projects, br...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a calculated column or a measure as below.

    Calculated column:

    Result Column = 
var edate = [ExtractionDate]
var project = [Project]
var year = [Year]
var quarter = [Quarter]
var _lastdate = 
CALCULATE(
    MAX('Table'[ExtractionDate]),
    FILTER(
        ALL('Table'),
        [ExtractionDate]<edate&&
        [Project]=project&&
        [Year]=year&&
        [Quarter]=quarter
    )
)
var _val = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        [ExtractionDate]=_lastdate&&
        [Project]=project&&
        [Year]=year&&
        [Quarter]=quarter
    )
)
return
IF(
    ISBLANK(_lastdate),
    BLANK(),
    [Value]-_val
)

     

    Measure:

    Result Measure = 
var tab = 
ADDCOLUMNS(
    'Table',
    "Result",
    var edate = [ExtractionDate]
    var project = [Project]
    var year = [Year]
    var quarter = [Quarter]
    var _lastdate = 
    CALCULATE(
        MAX('Table'[ExtractionDate]),
        FILTER(
            ALL('Table'),
            [ExtractionDate]<edate&&
            [Project]=project&&
            [Year]=year&&
            [Quarter]=quarter
        )
    )
    var _val = 
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            [ExtractionDate]=_lastdate&&
            [Project]=project&&
            [Year]=year&&
            [Quarter]=quarter
        )
    )
    return
    IF(
        ISBLANK(_lastdate),
        BLANK(),
        [Value]-_val
    )
)
return
SUMX(
    tab,
    [Result]
)

     

    Result:

     

    Best Regards

    Allan

     

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello Allan, v-alq-msft 

 

Thank you very much for your prompt reply.

 

Your proposal worked perfectly.

 

Please allow me two questions.

 

1) Can you please ellaborate more on the logic behind your solution? For example, I cannot really see at which point the subtraciton between the two extraction dates take place

2) Regarding the 1st extraction date, why are there results againt these rows?

 

Kind regards,

 

George

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

 

First, we need to calculate last date where 'ExtractDate', 'Project', 'Year', 'Quarter' all equal to the corresponding value in current row.

var _lastdate = 
    CALCULATE(
        MAX('Table'[ExtractionDate]),
        FILTER(
            ALL('Table'),
            [ExtractionDate]<edate&&
            [Project]=project&&
            [Year]=year&&
            [Quarter]=quarter
        )
    )

Then we may calculate the last value according to the last date where 'ExtractDate', 'Project', 'Year', 'Quarter' all equal to the corresponding value in current row.

var _val = 
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            [ExtractionDate]=_lastdate&&
            [Project]=project&&
            [Year]=year&&
            [Quarter]=quarter
        )
    )

 Finally we need to calculate the difference between current value and last value when last value is not blank.

IF(
        ISBLANK(_lastdate),
        BLANK(),
        [Value]-_val
)

 

Hope it helps for you to understand the logic.

 

Best Regards

Allan

 

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