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, how to create a Month column if the date is N-10 days prior current month and N-10 days prior next month.
For example:
Month | Start Date (N-10 days prior current month) | End Date (N-10 days prior next month) |
July | 21st June | 21st July |
August | 22nd July | 21st August |
September | 22nd August | 20th September |
Solved! Go to Solution.
Please try this column expression instead.
MonthColumn =
VAR thisdate = 'Date'[Date]
VAR daysfromend =
INT ( EOMONTH ( thisdate, 0 ) - thisdate )
VAR monthtoformat =
IF ( daysfromend <= 10, EOMONTH ( thisdate, 1 ), thisdate )
RETURN
FORMAT ( monthtoformat, "yyyy-mm" )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@PBI_newuser , If you have date
Then
Start Date = eomonth([Date],0) -10
End Date = eomonth([Date],1) -10
If you have month name create a date fist like
Date = "01-" & [Month] & "-" & [Year] // you can change data type to date
Hi @amitchandak , based on the start and end date, how to create the month column?
I have date field in my table.
Month | Start Date (N-10 days prior current month) | End Date (N-10 days prior next month) |
2021-07 | 21-6-2021 | 21-7-2021 |
2021-08 | 22-7-2021 | 21-8- 2021 |
2021-09 | 22-8-2021 | 20-9-2021 |
You can create a DAX column with an expression like this. Replace Table with your actual table name.
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @mahoneypat , actually I wanna group the date by month to calculate the some metrics.
For example, I want to calculate the performance of each team in each month.
If the date falls between start date and end date, then take the month of end date.
Date | Month |
25-6-2021 | 2021-07 |
15-7-2021 | 2021-07 |
22-7-2021 | 2021-08 |
10-8-2021 | 2021-08 |
31-8-2021 | 2021-09 |
20-9-2021 | 2021-09 |
21-9-2021 | 2021-10 |
Please try this column expression instead.
MonthColumn =
VAR thisdate = 'Date'[Date]
VAR daysfromend =
INT ( EOMONTH ( thisdate, 0 ) - thisdate )
VAR monthtoformat =
IF ( daysfromend <= 10, EOMONTH ( thisdate, 1 ), thisdate )
RETURN
FORMAT ( monthtoformat, "yyyy-mm" )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
106 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |