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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.