Comparison of 2 month with 2 previous month

[Isgandar_B]

Hi dears,

I have a issue with comparasion. Let me explain situation, let's go with data example from excel:

I want to calculate previous period, it's not any problem with 1 month selected, we can do this with sereval ways, and i already handle with this.

But when i try to select 2 month, if i use same formula, i get result with compairing 2 month vs 1 month, for example i select Apr'21 & May'21 i get previous period as Mar'21 only.

What i want: for example when I will select Apr'21 & May'21, I want that this period was compared with Feb'21 & Mar'21.

Which formula and how i must to use to get this result?

Thanks in advance.

[Isgandar_B]

Hi to everyone,

I'm solve my problem, and find solution which fully what i want.
Just want to share code below, maybe it will be usefull for someone, if you have questions let me know, code not perfect but it's solve my problem, and I'm only started working with Power BI.

Sales Previous Period =

VAR Start_Current_Period = FIRSTDATE(CalendarTable[Date])

VAR End_Current_Period = LASTDATE(CalendarTable[Date])

VAR Days_In_Period = DATEDIFF(Start_Current_Period,End_Current_Period,DAY) - 4

VAR End_Prev_Period = Start_Current_Period - 1

VAR Start_Prev_Period = End_Prev_Period - Days_In_Period

VAR Start_Prev_Period2 = EOMONTH(Start_Prev_Period, - 1) + 1

VAR Result_Prev_Period = CALCULATE(

    SUM(Data[Sales]),

    DATESBETWEEN(

        CalendarTable[Date],

        Start_Prev_Period2,

        End_Prev_Period)

)

VAR Result = DIVIDE(SUM(Data[Sales]),Result_Prev_Period)

RETURN

    Result

[v-shex-msft]

HI @Isgandar_B,

You can add variable to get the selected month count and use it as offset in Dax expressions for previous period calculations.

Regards,

Xiaoxin Sheng

[Isgandar_B]

Hi thanks for your reply, yes i think so, logic is clear
I need formula which will determine 2 previous month of selected period, so how i can write this formula, could you help me with guidance (some topics or videos maybe)
i need that formula dynamically understand that if i selected Mar'23 & Apr'23 previous period must ve Jan'23 & Feb'23

[v-shex-msft]

HI @Isgandar_B,

You can try to use the following measure formula if it suitable for your requirement:

previous by selection =
VAR _offset =
    COUNTROWS (
        DISTINCT (
            SELECTCOLUMNS (
                VALUES ( NewTable[Date] ),
                "YMonth",
                    YEAR ( [Date] ) * 100
                        + MONTH ( [Date] )
            )
        )
    )
VAR currDate =
    MAX ( Table[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - _offset, DAY ( currDate ) )
VAR prevValue =
    CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( Table ), [Date] = prevDate )
    )
RETURN
    DIVIDE ( SUM ( Table[Value] ) - prevValue, prevValue )

Regards,

Xiaoxin Sheng

[Isgandar_B]

Thanks for your reply, but issue is that i need currdate not a max in date table, i need period which will be selected by user.

And second issue is that some times it can be 2 month selected, but another case 4-6-8 month, 2 month i write just for example.