Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anhngv
Helper III
Helper III

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

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
Helper III
Helper III

Can anyone help me with this?

 

Brgds

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
Super User
Super User

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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors