Forum Discussion

mohdk52's avatar
mohdk52
New Member
4 years ago
Solved

Replicating current month column's data to future month

Hi team,   I am working on a project where there are sales value for Jan and Feb 2022. I have to add the data from Mar'22 to Dec'22 - considering the numbers from FEB'22 and multiplying them by 1....
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi mohdk52 ,

    According to your description, here's my solution.

    1.Create a new month table, don't make relationship with the fact table.

    2.In Power Query, add an index column.

    3.Create a measure.

     

    Sales Measure =
VAR _Sales =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = MAX ( 'Month'[Month] )
                && 'Table'[Region] = MAX ( 'Table'[Region] )
        ),
        'Table'[Sales]
    )
VAR _Last =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        NOT ISBLANK ( 'Table'[Sales] )
                            && 'Table'[Region] = MAX ( 'Table'[Region] )
                    ),
                    'Table'[Index]
                )
        ),
        'Table'[Sales]
    )
VAR _Diff =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = MAX ( 'Month'[Month] )
                && 'Table'[Region] = MAX ( 'Table'[Region] )
        ),
        'Table'[Index]
    )
        - MAXX (
            FILTER (
                ALL ( 'Table' ),
                NOT ISBLANK ( 'Table'[Sales] )
                    && 'Table'[Region] = MAX ( 'Table'[Region] )
            ),
            'Table'[Index]
        )
RETURN
    IF ( NOT ISBLANK ( _Sales ), _Sales, _Last * POWER ( 1.5, _Diff ) )

     

    Put the new month column, the measure and Region in the matrix, get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

mohdk52 , Try a measure like

calculate(lastnonblankvalue('Date'[Month Year], [meausre]), filter(all('Date'), 'Date'[Date] >= eomonth(today(),-1) && 'Date'[Date] <= max('Date'[Date])))

  • mohdk52's avatar
    mohdk52
    New Member
    4 years ago

    Thanks amitchandak But unfortunately it doesnt seem to be working.

    Apologies If i didnt give the proper example before but in below example table.

    Jan and Feb are actuals. And my dataset has data only for these 2 months.

    Mar numbers are derived from Feb*1.5.

    Apr numbers are derived from Mar*1.5.

    I need to have this into a matrix chart.

     

    Month/SalesJANFEBMARAPR
    Region12000300045006750
    Region23500250037505625

     

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi mohdk52 ,

      According to your description, here's my solution.

      1.Create a new month table, don't make relationship with the fact table.

      2.In Power Query, add an index column.

      3.Create a measure.

       

      Sales Measure =
VAR _Sales =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = MAX ( 'Month'[Month] )
                && 'Table'[Region] = MAX ( 'Table'[Region] )
        ),
        'Table'[Sales]
    )
VAR _Last =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]
                = MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        NOT ISBLANK ( 'Table'[Sales] )
                            && 'Table'[Region] = MAX ( 'Table'[Region] )
                    ),
                    'Table'[Index]
                )
        ),
        'Table'[Sales]
    )
VAR _Diff =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = MAX ( 'Month'[Month] )
                && 'Table'[Region] = MAX ( 'Table'[Region] )
        ),
        'Table'[Index]
    )
        - MAXX (
            FILTER (
                ALL ( 'Table' ),
                NOT ISBLANK ( 'Table'[Sales] )
                    && 'Table'[Region] = MAX ( 'Table'[Region] )
            ),
            'Table'[Index]
        )
RETURN
    IF ( NOT ISBLANK ( _Sales ), _Sales, _Last * POWER ( 1.5, _Diff ) )

       

      Put the new month column, the measure and Region in the matrix, get the correct result.

      I attach my sample below for reference.

       

      Best Regards,
      Community Support Team _ kalyj

      If this post helps, then please considerAccept it as the solution to help the other members find it more quickly.