Forum Discussion

luisreyes's avatar
luisreyes
Frequent Visitor
4 years ago
Solved

Display moth value in card instead total

Hi,

 

 

I have this table of employees with:

  • Name
  • Surname
  • Salary
  • ID Number
  • Department 
  • Gender
  • Date of payroll

i'm trying to make a dashboard for payroll i have a bar visual for every month payroll, and i want a card with the number of employees by month but tha data i can get is distinctive count of ID number but i get this data from all year.

 

Can i display a month for default value(preferably the last month)? but with the option of choose another month in filter.

Need Help
  • sturlaws's avatar
    sturlaws
    4 years ago

    Hi luisreyes,

     

    yes, if the model is as simple as you describe it, you can create a measure like this:

     

    NumberOfEmployees = 
VAR _lastMonth =
    CALCULATE ( MAX ( 'Table'[Month] ), ALL ( 'Table' ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[month] ),
        COUNT ( 'Table'[Employee] ),
        CALCULATE (
            COUNT ( 'Table'[Employee] ),
            FILTER ( ALL ( 'Table'[Month]) , 'Table'[Month] = _lastMonth )
        )
    )

     

     

    Cheers,

    Sturla

6 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    Hi luisreyes,

     

    yes, if the model is as simple as you describe it, you can create a measure like this:

     

    NumberOfEmployees = 
VAR _lastMonth =
    CALCULATE ( MAX ( 'Table'[Month] ), ALL ( 'Table' ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[month] ),
        COUNT ( 'Table'[Employee] ),
        CALCULATE (
            COUNT ( 'Table'[Employee] ),
            FILTER ( ALL ( 'Table'[Month]) , 'Table'[Month] = _lastMonth )
        )
    )

     

     

    Cheers,

    Sturla

    • luisreyes's avatar
      luisreyes
      Frequent Visitor
      4 years ago

      Sturla, your response is impressive, but i don't know is i'm doing well because only show the last month, i can't filter by any other month

       

      Look Without any filter

       

      And with february as filter

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Try this.  Create a Month slicer from the Calendar Table and select a certain month.  In the card visual, drag this measure

    Measure 1 = distinctcount(Data[ID Number])

    If you want to show the previous month's value, then write this measure

    Measure 2 = calculate([Measure 1],previousmonth(Calendar[date]))

    Hope this helps.

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    You might have to adapt how the code according to your model, e.g. if you have a date-table. 

    Here is what it looks like in my simple mockup report, without any selection on month:

     


    with a month selected:

     

    So, either play around with the code, or describe what your model looks like, and I'll have another look