Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Subtract previous month values from current month values using DAX

Hi Experts,

I am working on a scenario like to get the difference of the 2 months and apply into the card based on the month slicer selection.

Example : If i Select May month in the slicer , the values should be May - Apr.

Assume May month value is 39659866 , April month value is 39470667

So the vairance is 189199 (this should be in the card visual).

I don't have any proper date tables in my dashboard.

 

Please help to achieve this asap .. its friday 😞

 

thanks

DK

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I suggest having a proper calendar dimension table.

     

    I tried to create a sample pbix file like below.

     

    You can try to create table by using below DAX formula after clicking "New Table" button.

     

     

    And, create a relationship like below.

     

     

     

     

    Please check the attached pbix file.

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

     

    Sales vs. previous sales: = 
VAR _currentmonth =
    SUM ( sales[sales] )
VAR _previousmonth =
    CALCULATE (
        SUM ( sales[sales] ),
        OFFSET (
            -1,
            ALL ( 'calendar'[Month-Year], 'calendar'[Month-Year sort] ),
            ORDERBY ( 'calendar'[Month-Year sort], ASC )
        )
    )
RETURN
    _currentmonth - _previousmonth

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    I suggest having a proper calendar dimension table.

     

    I tried to create a sample pbix file like below.

     

    You can try to create table by using below DAX formula after clicking "New Table" button.

     

     

    And, create a relationship like below.

     

     

     

     

    Please check the attached pbix file.

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

     

    Sales vs. previous sales: = 
VAR _currentmonth =
    SUM ( sales[sales] )
VAR _previousmonth =
    CALCULATE (
        SUM ( sales[sales] ),
        OFFSET (
            -1,
            ALL ( 'calendar'[Month-Year], 'calendar'[Month-Year sort] ),
            ORDERBY ( 'calendar'[Month-Year sort], ASC )
        )
    )
RETURN
    _currentmonth - _previousmonth

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Jihwan_Kim  Wowww It saved me a lot 🙂

      Thanks for your solution 😍