Forum Discussion

DiepNhiep's avatar
DiepNhiep
Frequent Visitor
2 years ago
Solved

Dynamic previous Period comparison

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!

Need Help
  • DiepNhiep's avatar
    DiepNhiep
    2 years ago

    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 )
            )

5 Replies

  • audreygerred's avatar
    audreygerred
    Icon for Super User rankSuper User
    2 years ago

    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

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    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's avatar
      DiepNhiep
      Frequent Visitor
      2 years ago

      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 )
              )
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      DiepNhiep
      Frequent Visitor
      2 years ago

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