Power BI generate future date with last known value

0
votes

I need assistance to generate future date with the last known value in Power BI I have data as below:

Date variable value
Dec-20 PC 20000
Dec-20 PLOB 50000
Jan-21 PC 30000
Jan-21 PLOB 40000
Feb-21 PC 50000
Feb-21 PLOB 60000
Mar-21 PC 20000
Mar-21 PLOB 50000
Apr-21 PC 60000
Apr-21 PLOB 70000

I need to generate the below table:

Date variable value
Dec-20 PC 20000
Dec-20 PLOB 50000
Jan-21 PC 30000
Jan-21 PLOB 40000
Feb-21 PC 50000
Feb-21 PLOB 60000
Mar-21 PC 20000
Mar-21 PLOB 50000
Apr-21 PC 60000
Apr-21 PLOB 70000
May-21 PC 60000
May-21 PLOB 70000
: : :
Dec-21 PLOB 60000
Dec-21 PLOB 70000

Kindly assist me with DAX calculation

Thanks

1
powerbidaxpowerquery

1 Answers

0
votes

You can try the following, the steps are

  1. Create a list of the Present Months
  2. Calculate the maximum month
  3. Get the distinct categories
  4. Generate a list of all future months contain in 2021, and calculate with ADDCOLUMNS the start of the months.
  5. Take only the future months using EXCEPT
  6. Cross Join the future months with the distinct categories, that would give you all the combinations possible.
  7. ADDCOLUMNS to the CROSSJOIN table, where [value] is calculated using the [variable] name and the max month from the table you provided. VAR is used to access the row context, so we can use FILTER.

Calculated Table

Future =
VAR PresentMonths =
    ALL ( 'Table'[Date] )
VAR maxMonth =
    MAXX ( 'Table', [Date] )
VAR DistinctCat =
    ALL ( 'Table'[variable] )
VAR AllMonths =
    SUMMARIZE (
        ADDCOLUMNS (
            CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2021, 12, 31 ) ),
            "MonthStart", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 01 )
        ),
        [MonthStart]
    )
VAR FutureMonths =
    EXCEPT ( AllMonths, PresentMonths )
VAR FutureTable =
    ADDCOLUMNS (
        CROSSJOIN ( FutureMonths, DistinctCat ),
        "value",
            CALCULATE (
                SUM ( 'Table'[value] ),
                VAR localVariable = [variable]
                RETURN
                    FILTER ( 'Table', [variable] = localVariable && [Date] = maxMonth )
            )
    )
RETURN
    UNION ( 'Table', FutureTable )

Output

Date variable value
December 2020 PLOB 50000
December 2020 PC 20000
January 2021 PC 30000
January 2021 PLOB 40000
February 2021 PC 50000
February 2021 PLOB 60000
March 2021 PC 20000
March 2021 PLOB 50000
April 2021 PC 60000
April 2021 PLOB 70000
May 2021 PC 60000
May 2021 PLOB 70000
June 2021 PLOB 70000
June 2021 PC 60000
July 2021 PC 60000
July 2021 PLOB 70000
August 2021 PC 60000
August 2021 PLOB 70000
September 2021 PC 60000
September 2021 PLOB 70000
October 2021 PC 60000
October 2021 PLOB 70000
November 2021 PC 60000
November 2021 PLOB 70000
December 2021 PC 60000
December 2021 PLOB 70000