Forum Discussion

jdtobon88's avatar
jdtobon88
Icon for Helper I rankHelper I
5 years ago
Solved

Calculate next month in Pivot

Cheers, I require your support to obtain in a new column of a table at column level, the data of the following month and of the same reference. I share the reference image and in yellow color as the...
data protection
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    Try this calculated column formula

    =lookupvalue(data[units],data[date],calculate(min(data[date]),filter(data,data[sku]=earlier(data[sku])&&data[date]>earlier(data[date]))),data[sku],data[sku])

    Hope this helps.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    5 years ago

    Hi @ jdtobon88,

     

    Try this DAX expression :

    Column =
    CALCULATE(
        MAX([units]),
        FILTER('Table',
            DATEADD('Table'[Date],-1,MONTH)=EARLIER('Table'[Date]) 
            && [SKU]=EARLIER('Table'[SKU])
    )
)

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

v-chenwuz-msft's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi @ jdtobon88,

 

Try this DAX expression :

Column =
    CALCULATE(
        MAX([units]),
        FILTER('Table',
            DATEADD('Table'[Date],-1,MONTH)=EARLIER('Table'[Date]) 
            && [SKU]=EARLIER('Table'[SKU])
    )
)

 

Best Regards

Community Support Team _ chenwu zhu

 

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