Dynamic previous Period comparison

[DiepNhiep]

Hi all, I would like to get help on the dynamic date comparison. The example is as follow:

I have a Sales table with date, item name, and Sales.

Suppose the Date slicer to be set as 1 Sep 2023 to 1 Oct  2023. I would like to compare that with 1 Aug 2023 to 31 Aug 2023 (so it is like the same days in between but previous to the current period).

Or if the Date Slicer to be set as 14 Sep 2023 to 21 Sep 2023 (1 week), then compare to previous 6 Sep 2023 to 13 Sep 2023

I think the logic behind is to find the x days in between of StartDate and EndDate chosen in the Slicer, and from the StartDate - 1, we need to minus x days to get previous period but I have no idea how to do that with DAX.

Please help, thank you all!

[Ashish_Mathur]

Hi,

1 Sep - 1 Oct 2023 is 31 days.  Therefore shouldn't the previous period be 31 Jul - 31 Aug?  Why have you mentioned 1 Aug - 31 Aug?

[DiepNhiep]

That is true, I was in a hurry so it is like a miscalculations.

[v-zhangti]

Hi, @DiepNhiep 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

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

[DiepNhiep · Accepted solution]

Thank you, I found the solution by scrolling one of the previous questions in the community. It goes like this solving the problem for me:

LastPeriod =

VAR selectedDays =

    CALCULATE ( COUNT ( Calendar[Date] ), ALLSELECTED ( Calendar[Date] ) )

VAR minDate =

    CALCULATE ( MIN ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar'[Date] ) )

RETURN

        CALCULATE (

            SUM (SalesTable[SalesColumn] ),

            DATESINPERIOD ( 'Calendar'[Date], minDate - 1, - selectedDays, DAY )

        )

[audreygerred]

Hello! If you do not already have a date table in your model (and marked as a date table), I highly recommend that. Here is a blog post I wrote about that with a link to the date table I love using. http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/ 

For a MoM% calculation, you can use quick measures in Power BI - this is one of them (as well as YoY% and QoQ%). Here is also a link to some MoM measures from Reza Rad: Month over Month Calculation in Power BI using DAX - RADACAD and a link about week-over-week measures (and having the date table I suggested will make week-over-week a breeze): Week-related calculations – DAX Patterns and Week-Based Time Intelligence in DAX - SQLBI