Forum Discussion

Justas4478's avatar
Justas4478
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Fathers day comparison

Hello, I am trying to create report that compares last years fathers day sales with this years sales. The problem is that fathers day is on different day every year. Is there some table that has days when that fathers day is and what formula should I use to calculate difference between days so that I could compare them. Thank you in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Justas4478 

    Please refer to my example below.

     

    1. Lets if you have date column from 2018 to 2021, first enter the date of father's days in a new table.

    2.  Next, you can create a flag column something can be like this.

     

    3. Then you can just create table visual.

    4. To get the difference between this current year fathers day and last year fathers day, you may use following measure.

    Diff measure = 
var current_fathsday_sales= CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Fathers day]="Yes"))
var pre_fathsday_sales = CALCULATE(SUM([Sales]),FILTER(ALL('Table'),[Date]=CALCULATE(MAX([Date]),FILTER(ALL('Table'),[Date]<MAX([Date])&&[Fathers day]="Yes"))))
Return current_fathsday_sales-pre_fathsday_sales

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Justas4478 , You need to have a flag for the father day on the calendar

     

    and you can use a measure like 

     

    This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank]) && 'Date'[father day] =1 ))
    Last Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year Rank]=max('Date'[Year Rank])-1) && 'Date'[father day] =1 ))

    • Justas4478's avatar
      Justas4478
      Icon for Post Prodigy rankPost Prodigy
      5 years ago

      Sorry I am new to power bi. How do you create flag for it in the calendar?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Justas4478 

    Please refer to my example below.

     

    1. Lets if you have date column from 2018 to 2021, first enter the date of father's days in a new table.

    2.  Next, you can create a flag column something can be like this.

     

    3. Then you can just create table visual.

    4. To get the difference between this current year fathers day and last year fathers day, you may use following measure.

    Diff measure = 
var current_fathsday_sales= CALCULATE(SUM('Table'[Sales]),FILTER('Table',[Fathers day]="Yes"))
var pre_fathsday_sales = CALCULATE(SUM([Sales]),FILTER(ALL('Table'),[Date]=CALCULATE(MAX([Date]),FILTER(ALL('Table'),[Date]<MAX([Date])&&[Fathers day]="Yes"))))
Return current_fathsday_sales-pre_fathsday_sales

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.