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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors