Suggestion for formula calculates value at the end of last months

[carlobonan]

Hello everybody,
could you kindly help me.

I have used the following measures to calculate the maximum date of the previous month which will vary throughout the year with the periodic updating of the data. In this case the maximum date of the previous month is 29.01.2021 but with these measures that I have created, I cannot calculate the value correctly.

Are there any better formulas I can use?

(CALENDARIO is the name of my timetable)

01_C_LAST_MONTHS_A+B = CALCULATE(sum('01_DB_GLOBALE'[SaldoC]),DATESBETWEEN('CALENDARIO'[DATE],[Last_date_SaldoC in previous month],[Last_date_SaldoC in previous month]),'01_DB_GLOBALE','01_DB_GLOBALE'[Gestore]<>0,'01_DB_GLOBALE'[TIPO_PTF]="A"||'01_DB_GLOBALE'[TIPO_PTF]="B")

Last_date_SaldoC in previous month = CALCULATE(max('01_DB_GLOBALE'[DATA_CORRETTA]),CALCULATETABLE(datesbetween('CALENDARIO'[DATE],eomonth(today(),-2)+1,eomonth(today(),-1)),LASTNONBLANK('CALENDARIO'[DATE],CALCULATE([Total_SaldoC]))))

Total_SaldoC = SUM('01_DB_GLOBALE'[SaldoC])

Thanks

C.

[v-eqin-msft]

Hi @carlobonan ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Hope to hear from you. Thanks in advanced.😀

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @carlobonan ,

According to my understanding, you want to get the last friday of previous month, right?

Please try this:

Table =
ADDCOLUMNS (
    CALENDAR ( "2021/1/1", "2021/12/31" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "WeekDay", WEEKDAY ( [Date], 2 )
)

Add columns:

Last Friday =
CALCULATE (
    MAX ( 'Table'[Date] ),
    ALLEXCEPT ( 'Table', 'Table'[YearMonth] ),
    'Table'[WeekDay] = 5
)

Rank =
RANKX ( 'Table', [YearMonth],, ASC, DENSE )

Last Friday of Previous Month =
CALCULATE (
    MAX ( 'Table'[Last Friday] ),
    FILTER ( 'Table', [Rank] = EARLIER ( [Rank] ) - 1 )
)

The final output is shown below:

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[carlobonan]

@selimovd  

I try to explain myself better:

the end of the previous month, compared to the last date (19.02.2021) is 29.01.2021. The end date of the previous month will always be the last Friday of the month (29 / 01,26 / 02, 26/03) because the data is updated weekly.

[carlobonan]

@selimovd  hi the data is updated every Friday. I have to calculate:

- lastdate (ok measure works)

-lastmonth (formula does NOT work)

With the data of February I had to calculate the end of January (29/01/2021), with the data of March, I had to calculate the end of February (26/02/2021)

with the data of April I will have to calculate the end of March (26/03/21)

[selimovd]

Hello @carlobonan ,

I'm not sure if I understood the question right.

There is no 29.01.2021, so the measure calculated correctly. Or what exactly should have happened?

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi