Forum Discussion

adamsumm's avatar
adamsumm
Frequent Visitor
8 years ago
Solved

Creating a Card for displaying the previous months count

Hello,   I need to create two cards and a month slicer in my dashboard.  One card needs to coordinate with the month slicer and show me the current count of calls in that month.  The other card nee...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi adamsumm,

     

    The column opened_at contains time parts that isn't necessary in this scenario. The most important point is the time part is bad for the relationship.

    1. Create a new column:

    Relationship = [Opened_at].[Date]

    2. Establish relationship between this table and the date table.

    3. Two measures.

     

    CurrentMonthAmount = count(Table1[Number])
    PreviousMonthAmount =
VAR currentAmount =
    COUNT ( Table1[Opened_at] )
VAR previousAmount =
    CALCULATE ( COUNT ( 'Table1'[Opened_at] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF (
        previousAmount < currentAmount,
        CONCATENATE ( previousAmount, UNICHAR ( 9660 ) ),
        IF (
            previousAmount > currentAmount,
            CONCATENATE ( previousAmount, UNICHAR ( 9650 ) ),
            previousAmount
        )
    )

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi adamsumm,

 

The column opened_at contains time parts that isn't necessary in this scenario. The most important point is the time part is bad for the relationship.

1. Create a new column:

Relationship = [Opened_at].[Date]

2. Establish relationship between this table and the date table.

3. Two measures.

 

CurrentMonthAmount = count(Table1[Number])
PreviousMonthAmount =
VAR currentAmount =
    COUNT ( Table1[Opened_at] )
VAR previousAmount =
    CALCULATE ( COUNT ( 'Table1'[Opened_at] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF (
        previousAmount < currentAmount,
        CONCATENATE ( previousAmount, UNICHAR ( 9660 ) ),
        IF (
            previousAmount > currentAmount,
            CONCATENATE ( previousAmount, UNICHAR ( 9650 ) ),
            previousAmount
        )
    )

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

  • adamsumm's avatar
    adamsumm
    Frequent Visitor
    8 years ago

    This seems to work!   Thank you so much!  

     

    Follow up question though.  Is there a way to do the same thing but with a percentage of the total instead of a count?  

     

    thanks,  

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi adamsumm,

       

      Did you work it out? Maybe you can try it like this.

      % =
VAR total =
    CALCULATE ( COUNT ( 'Table1'[Opened_at] ), ALL ( 'Calendar' ) )
VAR currentAmount =
    COUNT ( Table1[Opened_at] )
VAR previousAmount =
    CALCULATE ( COUNT ( 'Table1'[Opened_at] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF (
        previousAmount < currentAmount,
        CONCATENATE ( previousAmount / total, UNICHAR ( 9660 ) ),
        IF (
            previousAmount > currentAmount,
            CONCATENATE ( previousAmount / total, UNICHAR ( 9650 ) ),
            previousAmount
        )
    )

      Or,

       

      % 2 =
VAR total =
    CALCULATE ( COUNT ( 'Table1'[Opened_at] ), ALL ( 'Calendar' ) )
VAR currentAmount =
    COUNT ( Table1[Opened_at] )
VAR previousAmount =
    CALCULATE ( COUNT ( 'Table1'[Opened_at] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF (
        previousAmount < currentAmount,
        CONCATENATE (
            CONCATENATE ( previousAmount / total * 100, "%" ),
            UNICHAR ( 9660 )
        ),
        IF (
            previousAmount > currentAmount,
            CONCATENATE (
                CONCATENATE ( previousAmount / total * 100, "%" ),
                UNICHAR ( 9650 )
            ),
            previousAmount
        )
    )

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Best Regards!

      Dale

  • quyenduong's avatar
    quyenduong
    Helper II
    3 years ago

    Thank you v-jiascu-msft  for the solution, is there anyway from this code, I can set the color red (negative), grey (blank or 0) and green (positive) for both # of differences and the unichar arrows? (see the screenshot below) 

     

    Thank you.
    amitchandak are there any chance you might know this?