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.
Here's my sample data, I have been creating some calculated columns that all look at the Date_Time field and determine it's academic year - I am trying to extend this to enable drill down on a monthly and daily basis, whereby September is month 1 through to month 12 August. However there's a discrepancy in the way the month is calculated...
Here's my data:
We can see something is going wrong in the Academic Year-Month column where the 12th January has become Month 5 - December (December should actually be Month 4)
Date_Time is the native date/time field.
Here's my formulas for the other calculated columns
Academic Year = IF(MONTH('Views and Created'[Date_Time])<9,YEAR('Views and Created'[Date_Time])-1&"/"&RIGHT(YEAR('Views and Created'[Date_Time]),2),YEAR('Views and Created'[Date_Time])&"/"&RIGHT(YEAR('Views and Created'[Date_Time])+1,2))This works fine.
Academic Year-Month = 'Views and Created'[Academic Year] & " - " & IF( IF(MONTH('Views and Created'[Date_Time])>8,MONTH('Views and Created'[Date_Time])-8,MONTH('Views and Created'[Date_Time])+4)<10,0,"" )& IF(MONTH('Views and Created'[Date_Time])>8,MONTH('Views and Created'[Date_Time])-8,MONTH('Views and Created'[Date_Time])+4) &" " & FORMAT(MONTH('Views and Created'[Date_Time]),"MMM")Lets break down how 12/01/2018 becomes "05 Dec". The first part works correctly. The statement looks at the date, sees that its January (01) - goes to the false part of the IF statement and adds 4 to this figure and it becomes 5. This is correct as January is month 5 of the academic year.
Academic Year-Month-Day = 'Views and Created'[Academic Year-Month] & " - " & IF(DAY('Views and Created'[Date_Time])<10,0,"") & DAY('Views and Created'[Date_Time])
Solved! Go to Solution.
So I couldn't see the woods for the trees - the reason the final statement didn't work is because I didn't need to use the month function here.
This:
& FORMAT(MONTH('Views and Created'[Date_Time]),"MMM")
Should be:
& FORMAT('Views and Created'[Date_Time],"MMM")
So I couldn't see the woods for the trees - the reason the final statement didn't work is because I didn't need to use the month function here.
This:
& FORMAT(MONTH('Views and Created'[Date_Time]),"MMM")
Should be:
& FORMAT('Views and Created'[Date_Time],"MMM")
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |