Forum Discussion

Mah_87's avatar
Mah_87
Frequent Visitor
3 years ago

Comparing Events this year Vs previous years with different date

Hi,
I have an issue calculating the comparison this year Vs previous years.
I want to have a page on which users can select events and get the result.
I want to compare the selected holiday with the same holiday last year. the issue is that holidays are not on the same date so the time intelligence functions not working
I got the correct result just when I filter my holiday table in the power query to have only one event! but when I want to expand to have all the events in the table then my measures do not work and give me the below error.

I used this measure:

*Holiday LY =
 VAR currentDate = SELECTEDVALUE('Date'[Date] )
           
VAR prevholiday =
    CALCULATE (
      VALUES( 'reference holidays_table'[Date] ),
        YEAR ( 'reference holidays_table'[date] )
            = YEAR ( currentDate ) - 1
    )
RETURN
    CALCULATE ( SUM ( Summary[Current]), 'Date'[Date] = prevholiday)



I think the problem is  VALUES( 'reference holidays_table'[Date] ) as it returns all the dates for each event and I need a way to only capture the selected date!
this is my data model

 


Thanks in advance

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Mah_87 , Make the join with date table single Directional. Mark date table as date table

     

    and use measures like 

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

     

     

    • Mah_87's avatar
      Mah_87
      Frequent Visitor
      3 years ago

      Thanks for your response
      did not work! my date table is already marked as a date table but PBI does not allow me to make a single direction!
      These calculations not worked. I want a slicer like this and when the user chooses each on it then the result shows for the last year based on the selected. but the dates for some of the holidays vary between years, it is not the same day last year for example Easter day!
      As mentioned my measure works fine as long as I have only one holiday like Boxing day in my data set and by choosing each of the boxing days in 2019,2020,2021,2022 from my holiday table it works fine, but when I remove the filter in Power query to have the rest of the holiday like the below snippet then my measure not work!

      Thanks