cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anhngv
Frequent Visitor

Calculate change value in matrix.

I have a problem with calculating change as below:

anhngv_0-1619611333401.png

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:

anhngv_1-1619613995373.png

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 🙂
1 ACCEPTED SOLUTION
anhngv
Frequent Visitor

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)

View solution in original post

4 REPLIES 4
anhngv
Frequent Visitor

Can anyone help me with this?

 

Brgds

anhngv
Frequent Visitor

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)

View solution in original post

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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:

anhngv_0-1619625721515.png

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors