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.5.

Kindly let me know how to achieve this?

 

Many thanks in advance.

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.

3 Replies

  • 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.