Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone, how are you?
I'm finding difficulties here to build a measure. I have 2 colums, one with the date and other with the value. I would like to create a new column or measure, to result in the difference between one date and its previous date.
But my problem is: the column date doesn't have equivalent subsequent dates, I don't have Saturdays and Sundays for example, and if I would like to have the difference between Monday and last Friday, I can't find any DAX formula to resolve it.
Here is a picture of my table and what I would like to resolve:
Is there any way that I can index my dates? Or I assume that the previous day is not necessary DAY - 1 ?
Thanks!
Solved! Go to Solution.
Hi @RodCamargoJr,
It seems like you need to find out hit nearest working days based on current date.
For his scenario, you can consider adding workday as condition to your expression and max function to find the biggest one from current date.
Measure formula:
formula =
VAR currDate =
MAX ( Table[Date] )
VAR prevDate =
CALCULATE (
MAX ( Table[Date] ),
FILTER (
ALLSELECTED ( 'Table' ),
WEEKDAY ( [Date], 2 ) <= 5
&& [Date] < currDate
)
)
RETURN
CALCULATE (
SUM ( Table[Value] ),
FILTER ( ALLSELECTED ( 'Table' ), Table[Date] = prevDate )
)
Regards,
Xiaoxin Sheng
VAR Value = sum(Table[Value])
VAR PreviousRow = CALCULATE( Value , OFFSET( -1, ALLSELECTED('Product'[Product]), ORDERBY('Product'[Product], ASC) ) )
RETURN Value - PreviousRow
If this post helps, please consider accept as solution to help other members find it more quickly.
Thank You!
Hi @RodCamargoJr,
It seems like you need to find out hit nearest working days based on current date.
For his scenario, you can consider adding workday as condition to your expression and max function to find the biggest one from current date.
Measure formula:
formula =
VAR currDate =
MAX ( Table[Date] )
VAR prevDate =
CALCULATE (
MAX ( Table[Date] ),
FILTER (
ALLSELECTED ( 'Table' ),
WEEKDAY ( [Date], 2 ) <= 5
&& [Date] < currDate
)
)
RETURN
CALCULATE (
SUM ( Table[Value] ),
FILTER ( ALLSELECTED ( 'Table' ), Table[Date] = prevDate )
)
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |