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! First time posting on here, will probably be posting more in the future as I am new to Power Bi.
My question is regarding forecasting and creating future quarter end dates. The project I am working on is essentially forecasting data for clients, but I don't know how to create any future quarter dates (3/31/2020, 6/30/2020, etc. all way up until 12/31/2030).
Is there a certain function to use?
Any help would be greatly appreciated!
Solved! Go to Solution.
Hi, @ValueCreate
You may create one more measure as below.
Result =
IF(
ISBLANK([futurequarterdate2]),
[futurequarterdate1],
IF(
ISBLANK([futurequarterdate3]),
[futurequarterdate1]&","&[futurequarterdate2],
IF(
ISBLANK([futurequarterdate4]),
[futurequarterdate1]&","&[futurequarterdate2]&","&[futurequarterdate3],
[futurequarterdate1]&","&[futurequarterdate2]&","&[futurequarterdate3]&","&[futurequarterdate4]
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @ValueCreate
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi, @ValueCreate
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi, @ValueCreate
Based on your description, I created data to reproduce your scenario.
Table:
Calendar(a calculated table):
Calendar = CALENDAR(DATE(2020,1,1),DATE(2020,12,31))
You may create four measures as follows.
futurequarterdate1 =
var _currentdate = MAX('Table'[Date])
var _result =
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
ALL('Calendar'),
QUARTER('Calendar'[Date]) = QUARTER(_currentdate)&&
YEAR('Calendar'[Date]) = YEAR(_currentdate)
)
)
return
_result
futurequarterdate2 =
var _currentdate = MAX('Table'[Date])
return
IF(
MONTH([futurequarterdate1]) = 12&&
DAY([futurequarterdate1]) = 31,
BLANK(),
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
ALL('Calendar'),
QUARTER('Calendar'[Date]) = QUARTER(_currentdate)+1&&
YEAR('Calendar'[Date]) = YEAR(_currentdate)
)
)
)
futurequarterdate3 =
var _currentdate = MAX('Table'[Date])
return
IF(
MONTH([futurequarterdate2]) = 12&&
DAY([futurequarterdate2]) = 31,
BLANK(),
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
ALL('Calendar'),
QUARTER('Calendar'[Date]) = QUARTER(_currentdate)+2&&
YEAR('Calendar'[Date]) = YEAR(_currentdate)
)
)
)
futurequarterdate4 =
var _currentdate = MAX('Table'[Date])
return
IF(
MONTH([futurequarterdate3]) = 12&&
DAY([futurequarterdate3]) = 31,
BLANK(),
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
ALL('Calendar'),
QUARTER('Calendar'[Date]) = QUARTER(_currentdate)+3&&
YEAR('Calendar'[Date]) = YEAR(_currentdate)
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft Thanks for the help I will take a look at this today! Quick question- do you have any idea how I can keep all the future dates in the same column? I'd like to do that rather than creating multiple columns for the dates.
Hi, @ValueCreate
You may create one more measure as below.
Result =
IF(
ISBLANK([futurequarterdate2]),
[futurequarterdate1],
IF(
ISBLANK([futurequarterdate3]),
[futurequarterdate1]&","&[futurequarterdate2],
IF(
ISBLANK([futurequarterdate4]),
[futurequarterdate1]&","&[futurequarterdate2]&","&[futurequarterdate3],
[futurequarterdate1]&","&[futurequarterdate2]&","&[futurequarterdate3]&","&[futurequarterdate4]
)
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Generally you would create a Calendar table using CALENDAR function.
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 | |
109 | |
89 | |
76 | |
67 |
User | Count |
---|---|
125 | |
111 | |
100 | |
83 | |
71 |