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,
Hope you are all well.
I used the following two calculations to get my Date Numbered column:
Solved! Go to Solution.
@Anonymous I think this measure may give you the correct result (edited to change the switch year to SELECTEDVALUE() instead of MIN())
DateNumbered =
VAR _maxDate_2020 =
CALCULATE (
MAX ( 'Table'[Date] ),
ALL ( 'Table'[Date] ),
YEAR ( 'Table'[Date] ) = 2020
)
VAR _maxDate_2019 =
CALCULATE (
MAX ( 'Table'[Date] ),
ALL ( 'Table'[Date] ),
YEAR ( 'Table'[Date] ) = 2019
)
VAR _dayspast =
SWITCH (
YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ),
2020,
CALCULATE (
DATEDIFF ( SELECTEDVALUE ( 'Table'[Date] ), _maxDate_2020, DAY ) + 1
),
2019,
CALCULATE (
DATEDIFF ( SELECTEDVALUE ( 'Table'[Date] ), _maxDate_2019, DAY ) + 1
),
BLANK ()
)
RETURN
_dayspast
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@Anonymous I think this measure may give you the correct result (edited to change the switch year to SELECTEDVALUE() instead of MIN())
DateNumbered =
VAR _maxDate_2020 =
CALCULATE (
MAX ( 'Table'[Date] ),
ALL ( 'Table'[Date] ),
YEAR ( 'Table'[Date] ) = 2020
)
VAR _maxDate_2019 =
CALCULATE (
MAX ( 'Table'[Date] ),
ALL ( 'Table'[Date] ),
YEAR ( 'Table'[Date] ) = 2019
)
VAR _dayspast =
SWITCH (
YEAR ( SELECTEDVALUE ( 'Table'[Date] ) ),
2020,
CALCULATE (
DATEDIFF ( SELECTEDVALUE ( 'Table'[Date] ), _maxDate_2020, DAY ) + 1
),
2019,
CALCULATE (
DATEDIFF ( SELECTEDVALUE ( 'Table'[Date] ), _maxDate_2019, DAY ) + 1
),
BLANK ()
)
RETURN
_dayspast
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@Anonymous , Not very clear, Try like
MaxDate =
var _max CALCULATE(MAX('Table'[Date]),ALL('Table'[Date]))
return
date(year(_max)-1,month(_max), day(_max))
DateNumbered = CALCULATE(DATEDIFF(SELECTEDVALUE('Table'[Date]),[MaxDate],DAY)+1)
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |