How to calculate Revenue for each day of Current Fiscal Week for this and last year?

[slavisha84]

Hello,
I have two tables, FiscalCalendar, and Revenue by date.
I am trying to create a breakdown by day for the current fiscal week of this year, and the same period for the last year. 
My FiscalCalendar table looks like this:

MyRevenueByDate table looks like this:

What I need to produce is Matrix or table that looks like this:

So this is specific for the current fiscal week which is 14.
GrowthPerDay is just a simple formula (this year - last year)/ last year. 
WeekToDate would be, for example, (Monday and Tuesday together for fiscal week 14 of this week) - Monday and Tuesday together for fiscal week 14 last Year) / (Monday and Tuesday together for fiscal week 14 of last year)
In Excel, this is pretty easy to produce with a pivot table and lookup formula but can this be done in DAX?
How would I create a view like this?

Here is the folder where I have a sample of data with .pbix file :
https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq

[v-jingzhang · Accepted solution]

Hi @slavisha84 

You could create below measures and put them in a table.

Revenue This Year = SUM(RevenueByDate[Revenue])


Revenue Last Year = CALCULATE(SUM(RevenueByDate[Revenue]),FILTER(ALL(FiscalCalendar[FiscalYear]),FiscalCalendar[FiscalYear]=SELECTEDVALUE(FiscalCalendar[FiscalYear])-1))


GrowthPerDay = DIVIDE(RevenueByDate[Revenue This Year]-[Revenue Last Year],[Revenue Last Year])


WeekToDateGrowth =
VAR thisYearValue =
    CALCULATE (
        SUM ( RevenueByDate[Revenue] ),
        FILTER (
            ALL ( FiscalCalendar ),
            FiscalCalendar[FiscalYear] = SELECTEDVALUE ( FiscalCalendar[FiscalYear] )
                && FiscalCalendar[FiscalWeek] = SELECTEDVALUE ( FiscalCalendar[FiscalWeek] )
                && FiscalCalendar[Date] <= MAX ( FiscalCalendar[Date] )
        )
    )
VAR lastYearValue =
    CALCULATE (
        SUM ( RevenueByDate[Revenue] ),
        FILTER (
            ALL ( FiscalCalendar ),
            FiscalCalendar[FiscalYear]
                = SELECTEDVALUE ( FiscalCalendar[FiscalYear] ) - 1
                && FiscalCalendar[FiscalWeek] = SELECTEDVALUE ( FiscalCalendar[FiscalWeek] )
                && FiscalCalendar[Date]
                    <= MAX ( FiscalCalendar[Date] ) - 364
        )
    )
RETURN
    DIVIDE ( thisYearValue - lastYearValue, lastYearValue )

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[amitchandak]

@slavisha84 , same weekday is typically 364 days behind

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8