Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NeonX91
Regular Visitor

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?

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee


@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] )

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

Eric_Zhang
Microsoft Employee
Microsoft Employee


@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
Not applicable

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

NeonX91
Regular Visitor

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
Regular Visitor

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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors