Creating a Card for displaying the previous months count

[adamsumm]

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.  

[v-jiascu-msft · Accepted solution]

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

[quyenduong]

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?

[adamsumm]

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]

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