Help on middle of calendar date to latest month

[Anonymous]

Hi,

I need to sum Sum_SIO from July 2022 up to Nov. Start point is July up to latest Month Year.

I cannot use dateadd(datesmtd(date),-5 months) since it starts from latest month year, in this case Nov 2022.

It should start from July 2022 in this case. If latest month year is Dec 2022 then sum of Sum SIO (Jul 2022 to Dec2022). If latest month year is May 2023 then sum of Sum_SIO (July 2022 to May 2023). If latest month year is Sept 2023 then sum SumSIO (July 2023 to Sep 2023). Notice that it start evey July of every year and must sum it until it reaches another July on the following year. 

[v-henryk-mstf]

Hi @Anonymous ,

Whether the advice given by @Jihwan_Kim  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Jihwan_Kim]

Hi,

I am not sure how your datamodelo looks like, but if you have a Dim-Calendar table and if it has a one to many relationship with Sales table, try something like below.

One of many ways is to try to use DATESBETWEEN DAX function with CALCULATED.

DATESBETWEEN function (DAX) - DAX | Microsoft Learn

expected measure: =
VAR _startdate =
    DATE ( 2022, 7, 1 )
VAR _lastdateinsalestable =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )
RETURN
    CALCULATE (
        SUM ( Sales[Revenue] ),
        DATESBETWEEN ( Calendar[Date], _startdate, _lastdateinsalestable )
    )

[Anonymous]

Hi,

Thanks for responding however, it is not fixed on 2022. It should start July every year

So if report month is:

Nov 2022 (same as today) = sum from July 2022 to Nov 2022

April 2023 (for next year) = sum from July 2022 to Apr 2023

August 2023 (for next year) = sum from July 2023 to Aug 2023

Nov 2023 (for next year) = sum from July 2023 to Nov 2023

Dec 2023 (for next year) = sum from July 2023 to Dec 2023

yeah I have a dim calendar table

[Jihwan_Kim · Accepted solution]

Hi,

I assume "report month" is today's month, not the selected month on the slicer by users.

Please try something like below whether it suits your requirement.

expected measure: =
VAR _lastdateinsalestable =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS () )
VAR _startyear =
    IF (
        MONTH ( _lastdateinsalestable ) >= 7,
        YEAR ( _lastdateinsalestable ),
        YEAR ( _lastdateinsalestable ) - 1
    )
VAR _startdate =
    DATE ( _startyear, 7, 1 )
RETURN
    CALCULATE (
        SUM ( Sales[Revenue] ),
        DATESBETWEEN ( Calendar[Date], _startdate, _lastdateinsalestable )
    )