Total SUM filtered by most recent date in a month

[Anonymous]

Hello,

I have a data set that is updated once or twice per month.

A project might appear in more month so the same raw might appear multiple times, this is why I only want to use the most recent date.

Previously, the data was updated monthly and I just used MONTH (TODAY () -1) or MONTH(TODAY() ) if I refer to current month. But if I have my data is updated updated on 05.09.2020 and 10.09.2020 the below formula that I use right now will return a blank. Please note that the below formula first checks if there is any data in the current month and calculates the SUM, but if there is no data in the current month, it calculates the total SUM from last month. 

What I need now, is just a formula that calculates the same thing, but for the most recent month.

Please see the current formula below:

2021 Implementing Office / Total Award =

VAR __Calc =

CALCULATE (

SUM ( PA[Sales incl VAT] ) / SUM ( PA[Sales Without VAT] ),

FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () ) )

)

RETURN

IF (

ISBLANK ( __Calc ),

CALCULATE (

SUM (PA[Sales incl VAT] ) / SUM ( PA[Sales Without VAT] ),

FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () - 1 ) )

), __Calc

)

Thanks so much!

[v-lili6-msft · Accepted solution]

hi @Anonymous 

You just need to add ALL in the formula as below:

2021 Implementing Office / Total Award =

VAR __Calc =

CALCULATE (

SUM ( PA[Sales incl VAT] ) / SUM ( PA[Sales Without VAT] ),

FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () ) )

)

RETURN

IF (

ISBLANK ( __Calc ),

CALCULATE (

SUM (PA[Sales incl VAT] ) / SUM ( PA[Sales Without VAT] ),

FILTER ( ALL(PA[Date Snapshot]), MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () - 1 ) )

), __Calc

)

By the way, you'd better use a dim date table in your mode, see more details as below:

https://radacad.com/do-you-need-a-date-dimension

if you still have the problem, just share your sample pbix file and your expected output, that will be a great help.

Regards,

lin

[amitchandak]

@Anonymous , better to have a month year column

Date Snapshot Month =format([Date Snapshot] , "MMM-YYYY")

Try measure like

CALCULATE (
lastnonblankvalue( PA[Date Snapshot] ,SUM (PA[Sales incl VAT] ) / SUM ( PA[Sales Without VAT] )),
allexcept(Table,Table(PA[Date Snapshot Month] ))
)

CALCULATE (
SUM (PA[Sales incl VAT] ) / SUM ( PA[Sales Without VAT] ), filter( Date, Date[date] = Max(PA[Date Snapshot]))
allexcept(Date,Date(Date[Month] ))
)