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 needs to display the count from the previous month. 

 

I have attempted to use the PREVIOUSMONTH funtion but because my open at column contains dates with the same time the function will not work.  I also tried pointing the function to my month table key but that also does not provide me with the results I am looking for.  

 

Any help would be greatly appreciated. 

 

thansk,  

 

Also, I would also like to get some formating for the previous month card to show if it was lower or higher but that is less important.  

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

4 Replies

  • 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?