Partial data for year over year comparison handling on DAX

Question

I have the following tables:

Revenue
Branch
Date

I have a table viz with branch name, % over last year for revenue

Here, my calculations are correct and numbers are correct as per requirement. DAX I am using:

% over Last Year = IFERROR(
    ([Revenue 2019 YTD] / [Total Revenue 2018 for YTD]) -1,
    BLANK())

Problem: For 3 branches, these branches were acquired mid-year on 2018 and only has data from July of 2018. When I am calculating data for % over last year, the numbers are incorrect for these branches as they only had partial data on 2018 and complete data ( Jan to current month) in 2019.

I need help on how I can calculate the % over last year also while considering the min date of 2018 for some branches that were acquired mid-year in 2018.

For the top 3 branches the values are incorrect as when I calculate % over last get the data considered is 2019 whole , but only partial 2018. I need the data to be compared 2019 partial as well for those branches. — Success Maharjan
For suppose a branch called KTM there is data since July 2018. Now I need to calculate % over last year with 2019 from July. But I have data from Jan to current month for 2019 for the same branch. I cannot delete data as it will affect other visuals. — Success Maharjan
You only want to use on your YoY measure the months where both years have values? Something like that? Expected ouput with sample data would help. — mxix

Success Maharjan Success Maharjan · Accepted Answer · 2019-09-19T06:02:17

The solution to my question:

This Year YTD branch Growth with Partial Data = 

var _thisyear = YEAR([Today])
var _currentweek = WEEKNUM([Today])

// last year min date
var _minweek =
  CALCULATE(
    SUMMARIZE(
       Revenue,
           "Min Date lY", CALCULATE(MIN(Revenue[weeknum])))
 , FILTER(WeekCalendar, WeekCalendar[CalendarYear] = _thisyear - 1
 ))

return 
    SUMx(
        SUMMARIZE(Revenue,
        Revenue[Weekkey],
        "Revenue YTD",
        CALCULATE(
            SUM(Revenue[Revenue]),
            FILTER(Revenue, Revenue[weeknum] <= _currentweek),
            FILTER(Revenue, Revenue[Year] = _thisyear),
            FILTER(Revenue, Revenue[weeknum] >= _minweek), GROUPBY(branch,     branch[Branchname])
            )
        ),
        [Revenue YTD]
    )

If anyone has any suggestions to my DAX, Please let me know as well.

Partial data for year over year comparison handling on DAX

1 Answers