Forum Discussion

emma313823's avatar
emma313823
Helper V
2 years ago
Solved

problem with measure

I have pulled in some tables from an actively used Access database which has one table reporting on values of checks which we receive. I want to create two measures from this table which I can use on cards which will show a total for the previous month and a total for the current month.

 

I do have a MASTER date table called Calendar_Dates and have created a relationship from this master date table to the tblIncomingCommissions Pay-Date.

 

I tried the following DAX for the current month, but it's telling me I have to many arguments.

 

CurrentMoComm = CALCULATE(SUM('tblIncomingCommissions'[Check_Value],
FILTER('Calendar_Dates',MONTH(Calendar_Dates[Date]) = MONTH(TODAY())),
FILTER('Calendar_Dates',YEAR(Calendar_Dates[Date] = YEAR(TODAY())))))
 
Could someone help me figure this out or if there is better way to achieve current and previous? 
 
I really like new card visual, where I can stack and would like to have these two measure to be stacked in the 'new card' visual.
 
 
Need Help
  • NaveenGandhi's avatar
    NaveenGandhi
    2 years ago

    emma313823 

    Filter accepts only one argument, If you need to provide 2 arguments(month and year) you need to use &&.

    Also for previous month use below dax.

    Prevmonth =  =
    CALCULATE(
    SUM('tblIncomingCommissions'[Check_Value]),
    FILTER(
    'Calendar_Dates',
    MONTH('Calendar_Dates'[Date]) = MONTH(TODAY())-1 &&
    YEAR('Calendar_Dates'[Date]) = YEAR(TODAY())
    )
    )

    But this will remain static(change each month automatically), But if you want the user to select what the current month is. Then create two slicers for year and month. When use select a year and a month the below dax will give current and previous month.

    CM = SUM('tblIncomingCommissions'[Check_Value])

    Previous month = calculate(CM, 

    PREVIOUSMONTH(datecolumn))

    If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

    Follow me on LinkedIn!!!

3 Replies

  • NaveenGandhi's avatar
    NaveenGandhi
    Memorable Member
    2 years ago

    Hi emma313823 

    please try below DAX.

    CurrentMoComm =
    CALCULATE(
    SUM('tblIncomingCommissions'[Check_Value]),
    FILTER(
    'Calendar_Dates',
    MONTH('Calendar_Dates'[Date]) = MONTH(TODAY()) &&
    YEAR('Calendar_Dates'[Date]) = YEAR(TODAY())
    )
    )


    If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

    Follow me on LinkedIn!!

    • emma313823's avatar
      emma313823
      Helper V
      2 years ago

      Could you help me a bit understanding what I did wrong?

       

      • NaveenGandhi's avatar
        NaveenGandhi
        Memorable Member
        2 years ago

        emma313823 

        Filter accepts only one argument, If you need to provide 2 arguments(month and year) you need to use &&.

        Also for previous month use below dax.

        Prevmonth =  =
        CALCULATE(
        SUM('tblIncomingCommissions'[Check_Value]),
        FILTER(
        'Calendar_Dates',
        MONTH('Calendar_Dates'[Date]) = MONTH(TODAY())-1 &&
        YEAR('Calendar_Dates'[Date]) = YEAR(TODAY())
        )
        )

        But this will remain static(change each month automatically), But if you want the user to select what the current month is. Then create two slicers for year and month. When use select a year and a month the below dax will give current and previous month.

        CM = SUM('tblIncomingCommissions'[Check_Value])

        Previous month = calculate(CM, 

        PREVIOUSMONTH(datecolumn))

        If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

        Follow me on LinkedIn!!!