Forum Discussion

PhMeDie's avatar
PhMeDie
Helper I
6 years ago
Solved

Previous Period Measure in Retail Calendar Logic

Hi, I'd appreciate your help on a DAX measure.   We are on a 4-4-5 calendar. I have a date table in which I have a column with the calendar date. Furthermore I have an additional column which indi...
Need Help
  • PhMeDie's avatar
    PhMeDie
    6 years ago

    Hi,

     

    in the end the following formula did the trick.

     

    Previous Year Revenue =

    CALCULATE (

    [Net Sales VAT excluded EUR],

    FILTER (all(Calendar_day),Calendar_day[Commercial Date_Next Year ] in values(Calendar_day[Calendar Date])))

     

    This approach requires that in the calendart able a second column is maintained containing the comparable date of last year.

     

    Thanks anyways for your help.

     

    Best,

     

    Philipp

     

     

lc_finance's avatar
lc_finance
Solution Sage
6 years ago
Hi Phil,


You can replace SAMEPERIODLASTYEAR with DATESBETWEEN.
This formula takes 2 inputs : the beginning date and the ending date.

You can calculate the beginning date by:
- Using MIN on your Date table
- Use LOOKUPVALUE to find the corresponding day one year ago

You can calculate the ending date by
- Using MAX on your Date table
- Use LOOKUPVALUE to find the corresponding date one year ago

Once you have your beginning date and ending date, you can pass them to the DATESBETWEEN formula and it should give you exactly what you want

Does this help you?

LC
Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com
  • PhMeDie's avatar
    PhMeDie
    Helper I
    6 years ago

    Hello,

    and thanks for your answer.

     

    It would resolve my problem partially. What if a user does not select continous dates, but maybe the 3 sundays in December and would like to compare them with the comparable sundays of the previous year? Then that formula wouldn't give me the right results.

    My intution is trying to tell me that it must be possible by somehow filtering the previous date column on all the dates which are in the current context. But I cannot figure out how to do it.

     

    • lc_finance's avatar
      lc_finance
      Solution Sage
      6 years ago

      Hi PhMeDie ,

       

       

      what you want to do is possible. You mentioned that 'I have a date table in which I have a column with the calendar date. Furthermore, I have an additional column which indicates the comparable date one year ago.' 

       

      When a user selects something in Power BI (for example via a slicer), he is filtering the full rows not just some cells. This means that when the user selects the 3 Sundays in December he is filtering 3 rows. And these rows have the column 'which indicates the comparable date one year ago' . We can use this column to find the amount for the previous year.

       

      Could you share a Power BI file? (using One Drive or another similar tool)

      Based on your current file, I can recommend how to best do that

       

      LC

       

       

      • PhMeDie's avatar
        PhMeDie
        Helper I
        6 years ago

        Hi,

         

        in the end the following formula did the trick.

         

        Previous Year Revenue =

        CALCULATE (

        [Net Sales VAT excluded EUR],

        FILTER (all(Calendar_day),Calendar_day[Commercial Date_Next Year ] in values(Calendar_day[Calendar Date])))

         

        This approach requires that in the calendart able a second column is maintained containing the comparable date of last year.

         

        Thanks anyways for your help.

         

        Best,

         

        Philipp