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...
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 )
            )
audreygerred's avatar
audreygerred
Super 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