Forum Discussion

ValueCreate's avatar
ValueCreate
Icon for Helper I rankHelper I
6 years ago
Solved

Creating Future Quarter Dates

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 w...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

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.

ValueCreate's avatar
ValueCreate
Icon for Helper I rankHelper I
6 years ago

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.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.