Date table creating values for every day instead of just month

[bleumagnolia]

Help! I am a newbie to Power BI and have run into 2 issues when attempting to calculate revenue growth against prior month. The first is that I only have value for each month, so Power BI is placing the prior month revenue for every day of the month as I have linked the revenue table to the date table. How do i suppress all of the days except the last day of the month. See the row with the 7.1% in the Revenue Growth PM column. That is the only correct record and there should only be one row for each month.

The second issue is the incorrect total for Revenue PM. How can I total all the unique Revenue PM to arrive at the correct total value?

TIA!!

[bleumagnolia]

@VahidDM Not sure my reply to you went through as I don't see it on here, so apologies for the duplicate:

Here are the following DAX measures I created:

Revenue = sum(PTekFinancials[Revenue])

Revenue PM = calculate(sum(PTekFinancials[Revenue]),PREVIOUSMONTH('Calendar'[Date]))

Revenue Growth PM = calculate(divide(([Revenue]-[Revenue PM]),[Revenue PM]))

Here is the relationship between the date table and the data table - one to many as the data table only contains one record for each month for each company. Also included a snippet of the data table for reference as well.

Thank you!

[VahidDM]

HI @bleumagnolia 

What is the relationship between those tables? have you changed that relation to see the result?

Appreciate your Kudos!!

[bleumagnolia]

Hi @VahidDM , for some reason, I kept getting an error when I tried to reply directly to your message, so had to reply outside of the direct reply.

Thanks!

[v-kelly-msft · Accepted solution]

Hi  @bleumagnolia ,

I made a simple sample and create a measure as below:

_Revenue PM =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Month] = MAX ( 'Calendar'[Month] )
                && 'Calendar'[Year] = MAX ( 'Calendar'[Year] )
        )
    )
VAR _sum =
    CALCULATE ( SUM ( 'Table'[Revenue] ), PREVIOUSMONTH ( 'Calendar'[Date] ) )
RETURN
    IF ( MAX ( 'Calendar'[Date] ) = _maxdate, _sum )

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!