Calculate change value in matrix.

[anhngv]

I have a problem with calculating change as below:

My data has 2 time series (2 dimension date tables): 'Exdate' is dates that I collect Filght data in the future. 'FltDate' is data I collect on each 'Exdate' for next 30 days from 'Exdate'. And 'Days Pr' is calculated = FltDate - Exdate.

The 1st Matrix shows Dynamic values which are recorded each day from 30~0 (columns) prior to a Flightdate (rows).

E.g: 1st row of the Matrix shows Dynamic Values on 30 to 0 (value collected from 15Mar21 to 14Apr) day prior to the Flightdate 14Apr21

I would like to create a 2nd Matrix that shows Change between every 2 consecutive Day prior, which it should be:

I try this DAX: 

DynamicChg = [Dynamic] - CALCULATE([Dynamic],Advbkg[Days Pr] + 1)

but it doesn't work.

I'm looking for your help.

Many thanks 🙂

[anhngv]

Can anyone help me with this?

Brgds

[anhngv · Accepted solution]

It's quite difficult but I made it myself:

- Create a new column in database 'AP-1' = 'Days Pr' - 1

- Create a Dimension table =  Days Pr

- Create an active relationship between 2 'Days Pr' in 2 tables and an inactive relationship between 'Days Pr' and 'AP-1'

- Since the axis of the matrix is Days Pr of Dimension table, use this DAX:

DynamicChg =

var TD = [Dynamic]

var LD = CALCULATE([Dynamic], USERELATIONSHIP(Advbkg[AP-1], DayPr[DayPr]))

return IF(OR(TD = 0 , LD = 0) ,BLANK(),TD - LD)

[amitchandak]

@anhngv , with help from date table and time intelligence

This Day = CALCULATE([Dynamic], FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE([Dynamic], FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE([Dynamic], previousday('Date'[Date]))

diff =[This Day] - [Last Day]

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

[anhngv]

@amitchandak 

I thought about Time intelligence and tried some before but still not made it work.

I have tried your suggestion but still not get it done:

'Exdate' is a dimension date table which includes dates that we collect data for future Flightdate every day