Matrix visual to show difference of sum between two consecutive date

[Anonymous]

Hello Folks,

I have a table which shows sum of the value between ID and run date below:

But I want to create matrix visual to show difference of sum between two consecutive dates. I'm trying to apply PREVIOUSMONTH function but the problem is my data isn't on monthly basis so it's failed from comparison. For example two consecutive dates are 8/31 and 10/1 which we don't have data in September

Here is the exepected output:

What I've tried and still can't find the correct solution is:

CALCULATE(SUM(FIELD), FILTER(ALL(TABLE_NAME[DATE]), TABLE_NAME[DATE] < TABLE_NAME[DATE]))

But as a result, it returns more than one months which isn't the difference two month from incorrect condition. Do you have any idea on this? Or how should I refine the above calculation?

[Anonymous · Accepted solution]

Notes:

"Transactions" is a simple table with only one column named "TransactionDate" with 3 dates in it.

"PreviousDate" is a measure.

The last line "

IF(ISBLANK(PD),"",PD)" is not necessary. You can just use "RETURN PD". It is written as 

IF(ISBLANK(PD),"",PD) to ensure that the TransactionDate with blank PreviousDate is also shown in the visual.

[Anonymous]

Thanks for your help. Is there any way to combine it within FILTER expression? I need to use it in part of CALCULATE expression. For such 

CALCULATE(SUM(FIELD), FILTER EXPRESSION)

[Anonymous]

"Previous Date" is a measure that gives you the previous date against any date.

So if you have a visual with all the transaction dates, you can refer to this PreviousDate measure to get the previous date of each of them.

For example,

Sales =
VAR PrevDate = [PreviousDate]
VAR SalesAmount =
    SUMX (
        FILTER (
            ALLSELECTED ( TransactionTable ),
            TransactionTable[TransactionDate] = PrevDate
        ),
        TransactionTable[Amount]
    )
RETURN
    SalesAmount

 If you add this measure to a matrix with all your transaction dates, this measure will give you the sales amount of the previous date against each transaction date.

[Anonymous]

Ok, I'm clear more on how to get the previous date. But when I applied it with expression like this, it doesn't filter to the previous date to me. As a result, it returns only 0 which I guess that it can't find the data on that date.

Here is I adapted from: PowerBI: Stacked Column Chart using only measures (linkedin.com)

Switch Measure Trend = CALCULATE(
SUMX('Axis', SWITCH([AUX Measure], 
1, [Count DIM1], 
2, [Count DIM2], 
3, [Count DIM3] ) 
), FILTER(ALL(TABLE[DATE]), TABLE[DATE] = [PreviousDate]))

[Anonymous]

Please note the following...

1) The measure "PreviousDate" gives the result only when it is used in a context where there is only one "TransactionDate" as shown in your initial post.

2) For example, if a visual has "TransactionDate" on rows or columns or on any axis, "SELECTEDVALUE" will return the one date that is visible in the current context. If there is more than one transaction date in the current context, it fails. See the documentation of "SELECTEDVALUE" for more details.

3) A measure that uses the measure "PreviousDate" within it, actually relies on the fact that there only one "TransactionDate" in the current context. You have to use the measure in a visual that meets this criterion. For example, a matrix visual with the transaction dates on the rows, etc...

If you are not able understand the points mentioned above, please post the following here... I will take a look and try to provide a solution.

1) The DAX code of the measure [AUX Measure]

2) The DAX code of the measure [PreviousDate]

3) The table structures

4) An image of the visual that gives you the wrong results.

5) If possible a sample pbix file also.

[Anonymous]

Very thanks! Finally, I revised my expression based on your explanation to get the result. What I actually want is the difference before current date and previous date in the matrix visual. So, I wrote the expression like this to make it compare to the previous date.

Switch Measure Trend = 
VAR LAST_PREVIOUS_DATE = [PreviousDate]
RETURN 
SUMX('Axis', 
                    SWITCH([AUX Measure], 
                        1, [Count DIM1],
                        2, [Count DIM2],
                        3, [Count DIM3],
    )
)
-
CALCULATE(SUMX('Axis', 
                    SWITCH([AUX Measure], 
                        1, [Count DIM1],
                        2, [Count DIM2],
                        3, [Count DIM3],
    )
), TABLE[DATE] = LAST_PREVIOUS_DATE)