Final Value of Month

[ElliotP]

Afternoon,

I'm trying to calculate the percentage change between two concurrent months for example; I'm comfortable with the overall setup of that, but I'm not sure how to only do it for the final value of the month for the respective months.

I've tried EODMonth, LastDate, etc but I'm not sure how to only pull the final date of each month.

[TomMartens · Accepted solution]

Hey,

here you will find a pbix file that contains a little example to answer your question.

Please be ware that the solution also relies on a Calendar table, related to the fact table. Besides the Date, Year-Month, and Year column, this table also contains a column that numbers the Months meaning that all days of the first month are marked with 1.

All the measures contained defined in the Calendar table are not necessary for the final calculation.

The fact table contains two measures: "growth MoM Latest Value" and "Final growth MoM Latest Value" the first one calculates everything in one go, whereas the 2nd one uses the interemediate measures "ms ... curr Month" and "ms ... prev Month".

Here is a litte screenshot that shows the base data

and here is a screenshot from a table that also contains all the measures

And here is one of the measures

growth MoM Latest Value = 
var factcurrentRunningMonth = MAX('Calendar'[runnning Month])
var factLatestDateCurrentMonth = 
    CALCULATE(
        MAX('simple fact'[Date])
        ,FILTER(
            ALL('Calendar')
            ,'Calendar'[runnning Month] = factcurrentRunningMonth
        )
    )
var factLatestDatePrevMonth = 
    CALCULATE(
        MAX('simple fact'[Date])
        ,FILTER(
            ALL('Calendar')
            ,'Calendar'[runnning Month] = factcurrentRunningMonth -1
        )
    )
var latestvalueCurrMonth =
CALCULATE(
    SUM('simple fact'[Amount])
    ,FILTER(
        ALL('Calendar')
        ,'Calendar'[Date] =  factLatestDateCurrentMonth
    )
)
var latestvaluePrevMonth =
CALCULATE(
    SUM('simple fact'[Amount])
    ,FILTER(
        ALL('Calendar')
        ,'Calendar'[Date] = factLatestDatePrevMonth
    )
)
return
DIVIDE(latestvalueCurrMonth, latestvaluePrevMonth, BLANK())

Hope this is what you are looking for

Regards

Tom

[TomMartens]

Hey,

wondering what you mean by "Final Value of the Month"?

Is this the latest date for a certain measure, eg today would be the 2018-04-04 or
is the final value the aggregated value from 2018-04-01 to 2018-04-04?

How do you want to compare the April value to the March value? Have the same number of days to be considered?

Regards
Tom

[ElliotP]

@TomMartensthanks for the quick repsonse. The latest day of each month.

So for example, for Feb 2018 it would be the 28/2. For this month it would be the 4th (Current date for me is the 4/4).

The same number of days don't have to be considered, I saw an example on radacad but this should be a little bit nicer.

[TomMartens]

Hey,

thanks for the clarification, i assume that it would be 27/2 if 28/2 has no value?

Due to traveling my next answer will be delayed 😉

Regards
Tom