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, broken down per year/quarter, between two different dataset extractions. Table 1 is a simplistic version of my dataset, and table 2 resembles what I would like to produce. Table1 : Raw Data TableExtractionDateProjectYearQuarterValue01/09/2020PR1202019501/09/2020PR1202028101/09/2020PR1202037001/09/2020PR1202044001/09/2020PR2202013201/09/2020PR2202027401/09/2020PR2202035701/09/2020PR2202046501/12/2020PR1202019501/12/2020PR1202028101/12/2020PR1202038001/12/2020PR1202046901/12/2020PR2202013201/12/2020PR2202027401/12/2020PR2202037301/12/2020PR22020480 Table2 : Outcome TableExtractionDateProjectYearQuarterValueValue Delta01/12/2020PR12020195001/12/2020PR12020281001/12/2020PR120203801001/12/2020PR120204692901/12/2020PR22020132001/12/2020PR22020274001/12/2020PR220203731601/12/2020PR2202048015 Any help would really be appreciated. Thank you, George

Forum Discussion

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, broken down per year/quarter, between two different dataset extractions.

Table 1 is a simplistic version of my dataset, and table 2 resembles what I would like to produce.

Table1 : Raw Data Table

ExtractionDate	Project	Year	Quarter	Value
01/09/2020	PR1	2020	1	95
01/09/2020	PR1	2020	2	81
01/09/2020	PR1	2020	3	70
01/09/2020	PR1	2020	4	40
01/09/2020	PR2	2020	1	32
01/09/2020	PR2	2020	2	74
01/09/2020	PR2	2020	3	57
01/09/2020	PR2	2020	4	65
01/12/2020	PR1	2020	1	95
01/12/2020	PR1	2020	2	81
01/12/2020	PR1	2020	3	80
01/12/2020	PR1	2020	4	69
01/12/2020	PR2	2020	1	32
01/12/2020	PR2	2020	2	74
01/12/2020	PR2	2020	3	73
01/12/2020	PR2	2020	4	80

Table2 : Outcome Table

ExtractionDate	Project	Year	Quarter	Value	Value Delta
01/12/2020	PR1	2020	1	95	0
01/12/2020	PR1	2020	2	81	0
01/12/2020	PR1	2020	3	80	10
01/12/2020	PR1	2020	4	69	29
01/12/2020	PR2	2020	1	32	0
01/12/2020	PR2	2020	2	74	0
01/12/2020	PR2	2020	3	73	16
01/12/2020	PR2	2020	4	80	15

Any help would really be appreciated.

Thank you,

George

Need Help

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.

value comparison between two dates.pbix40 KB

3 Replies

v-alq-msft

Community Support

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.

value comparison between two dates.pbix40 KB

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
  Community 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.

Forum Discussion

value comparison between two dates

3 Replies

Related content

Comparison Chart

BI Tools Comparison

Dynamic comparison of values

Comparison Table

Table Value Comparison