Forum Discussion

NeonX91's avatar
NeonX91
Regular Visitor
8 years ago
Solved

Show data for Current month

Hi,

 

I know there are alot of similair questions, however none seem to address my specific question.

 

I have a date table and an object table
Each object has a date it was created.

Using count formulas, I can easily display how many objects were created per month, this is no problem.

January = 6
Feburaby = 7
March = 10 etc

 

However, I'm trying to do the following and I can't seem to get the right output.

1. Have a 'card' that displays the current month (dynamic)
2. Have a card that displays the previous month (dynamic)
3. Have a card that displays these as a % difference. (dynamic)

 

I can't even get past the first step.
I'm using the measure below

 

CurrentMonth = CALCULATE(COUNT(XXX[Number]),DATEADD('Date'[Month],0,MONTH))
However this measure is just showing the TOTAL count of objects, not the TOTAL count of objects for THIS month.

Any ideas?

  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago
    NeonX91 wrote:

    Hi,

     

    I know there are alot of similair questions, however none seem to address my specific question.

     

    I have a date table and an object table
    Each object has a date it was created.

    Using count formulas, I can easily display how many objects were created per month, this is no problem.

    January = 6
    Feburaby = 7
    March = 10 etc

     

    However, I'm trying to do the following and I can't seem to get the right output.

    1. Have a 'card' that displays the current month (dynamic)
    2. Have a card that displays the previous month (dynamic)
    3. Have a card that displays these as a % difference. (dynamic)

     

    I can't even get past the first step.
    I'm using the measure below

     

    CurrentMonth = CALCULATE(COUNT(XXX[Number]),DATEADD('Date'[Month],0,MONTH))
    However this measure is just showing the TOTAL count of objects, not the TOTAL count of objects for THIS month.

    Any ideas?

    You can try create 3 measure as below and put them to card visuals.

    currentMonth =
SUMX (
    FILTER (
        table,
        DATE ( YEAR ( table[date] ), MONTH ( table[date] ), 1 )
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
    ),
    table[amount]
)


previousMonth =
SUMX (
    FILTER (
        table,
        DATE ( YEAR ( table[date] ), MONTH ( table[date] ), 1 )
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
    ),
    table[amount]
)


diff % =
DIVIDE ( [currentMonth] - [previousMonth], [previousMonth] )

     

     

5 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago
    NeonX91 wrote:

    Hi,

     

    I know there are alot of similair questions, however none seem to address my specific question.

     

    I have a date table and an object table
    Each object has a date it was created.

    Using count formulas, I can easily display how many objects were created per month, this is no problem.

    January = 6
    Feburaby = 7
    March = 10 etc

     

    However, I'm trying to do the following and I can't seem to get the right output.

    1. Have a 'card' that displays the current month (dynamic)
    2. Have a card that displays the previous month (dynamic)
    3. Have a card that displays these as a % difference. (dynamic)

     

    I can't even get past the first step.
    I'm using the measure below

     

    CurrentMonth = CALCULATE(COUNT(XXX[Number]),DATEADD('Date'[Month],0,MONTH))
    However this measure is just showing the TOTAL count of objects, not the TOTAL count of objects for THIS month.

    Any ideas?

    You can try create 3 measure as below and put them to card visuals.

    currentMonth =
SUMX (
    FILTER (
        table,
        DATE ( YEAR ( table[date] ), MONTH ( table[date] ), 1 )
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
    ),
    table[amount]
)


previousMonth =
SUMX (
    FILTER (
        table,
        DATE ( YEAR ( table[date] ), MONTH ( table[date] ), 1 )
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
    ),
    table[amount]
)


diff % =
DIVIDE ( [currentMonth] - [previousMonth], [previousMonth] )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Nice solution.  Worked for me!  I also built a few using the CountX instead of SumX.

  • NeonX91's avatar
    NeonX91
    Regular Visitor
    8 years ago

    Hi,

     

    I know there are alot of similair questions, however none seem to address my specific question.

     

    I have a date table and an object table

    Each object has a date it was created.

     

    Using count formulas, I can easily display how many objects were created per month, this is no problem.

     

    January = 6

    Feburaby = 7

    March = 10 etc

     

    However, I'm trying to do the following and I can't seem to get the right output.

     

    1. Have a 'card' that displays the current month (dynamic)

    2. Have a card that displays the previous month (dynamic)

    3. Have a card that displays these as a % difference. (dynamic)

     

    I can't even get past the first step.

    I'm using the measure below

     

    CurrentMonth = CALCULATE(COUNT(XXX[Number]),DATEADD('Date'[Month],0,MONTH))

    However this measure is just showing the TOTAL count of objects, not the TOTAL count of objects for THIS month.

     

    Any ideas?

  • NeonX91's avatar
    NeonX91
    Regular Visitor
    8 years ago

    Hi,

    I know there are alot of similair questions, however none seem to address my specific question.

    I have a date table and an object table
    Each object has a date it was created.

    Using count formulas, I can easily display how many objects were created per month, this is no problem.

    January = 6
    Feburaby = 7
    March = 10 etc

     

    However, I'm trying to do the following and I can't seem to get the right output.

    1. Have a 'card' that displays the current month (dynamic)
    2. Have a card that displays the previous month (dynamic)
    3. Have a card that displays these as a % difference. (dynamic)

     

    I can't even get past the first step.
    I'm using the measure below

     

    CurrentMonth = CALCULATE(COUNT(XXX[Number]),DATEADD('Date'[Month],0,MONTH))
    However this measure is just showing the TOTAL count of objects, not the TOTAL count of objects for THIS month.

    Any ideas?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello all. I have a similar question. I also have a date table. I need to be able to show how many cancelled requests for current and previous month. I cannot figure how the orginal poster formulat to display how many objects were created per month. I will create a card in Power Bi but I need to be able to show in a card how many requests were cancelled for the previous month. Could you please provide the formula you used to group? Thanks

    January = 6
    Feburaby = 7
    March = 10