Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to 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:
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:
@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...
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
54 | |
35 | |
19 | |
19 | |
15 |