Forum Discussion

Merle's avatar
Merle
Frequent Visitor
2 years ago
Solved

Finding the difference between date values in the same row of different column within a Matrix

Hi,

I will greatly appreciate help in finding the difference between dates values within a matrix inorder to determine WoW%.

Desired out put in screen shot below.

This visual was obtain using the new tool New Calculation(fx) but has limitation of not being able to export data with these calculations.

These are the calculation used.

Versus previous = [Purchase Complete] - PREVIOUS([Purchase Complete], COLUMNS).
WOW = FORMAT(DIVIDE([Versus previous], PREVIOUS([Purchase Complete],COLUMNS),0),"#,#0%").

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Joe_Barry Thanks for your contribution on this thread.

    Hi Merle ,

    You can create a measure as below to get it, please find the details in the attachment.

    WOW = 
VAR _pcdate =
    SELECTEDVALUE ( 'Table'[Purchase Complete] )
VAR _category =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Purchase Complete] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] < _pcdate
        )
    )
VAR _prevalue =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] = _predate
        )
    )
RETURN
    DIVIDE ( SUM ( 'Table'[Sales] ) - _prevalue, _prevalue )

    Best Regards

7 Replies

  • Merle's avatar
    Merle
    Frequent Visitor
    2 years ago

    Hi Joe,

    Thanks for your response. Dax Formula used is giving the same value as the date column not the difference between the column dates of the previous. Is there a different way to format this formula? Thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Joe_Barry Thanks for your contribution on this thread.

      Hi Merle ,

      You can create a measure as below to get it, please find the details in the attachment.

      WOW = 
VAR _pcdate =
    SELECTEDVALUE ( 'Table'[Purchase Complete] )
VAR _category =
    SELECTEDVALUE ( 'Table'[Category] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Purchase Complete] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] < _pcdate
        )
    )
VAR _prevalue =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = _category
                && 'Table'[Purchase Complete] = _predate
        )
    )
RETURN
    DIVIDE ( SUM ( 'Table'[Sales] ) - _prevalue, _prevalue )

      Best Regards