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

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?