Forum Discussion

PhMeDie's avatar
PhMeDie
Icon for Helper I rankHelper 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
Icon for Solution Sage rankSolution 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
Icon for Helper I rankHelper 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