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 t...
Need Help
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