How to aggregate revenue by fiscal week?

[slavisha84]

Hello, 
I have one dataset with 2 columns: Date and Revenue. The Date column contains dates from 2020-01-06 to 2021-12-31. The second column contains Revenue data. 

I have another dataset in which I have a fiscal calendar table for the same data range as the first dataset. 

What I need t do is to merge these two datasets into a new table using the date as a cross-reference, and after that transform that new table to look like this:

FiscalWeek	2020 Revenue	2021 Revenue
1	747	732
2	720	544
3	739	925
4	667	652
…	…	…

So basically I need to separate revenue for 2020 and 2021 by fiscal week. 
Since there are multiple records for each date I am assuming I will need to do the sum.

What would be the easiest way to do this?

If you need sample data please I am uploading it to the shared folder below:

https://1drv.ms/u/s!AhhZq1add5YwjYIvuASi76lCL3R1eA?e=sZQshq

[jdbuchanan71]

@slavisha84 

Thank you for the sample .pbix, it makes it much easier to help.  All you need to is join the calendar table to the revenue table by the date field.

Then write a measure to sum the revenue.

Revenue Amount = SUM ( RevenueByDate[Revenue] )

And add the values from your calendar table and the measure to a visual.

I have attached my updated version of your sample so you can look at it.

[slavisha84]

@jdbuchanan71 
Thank you for your quick response. So what I need is an actual real new table because I will be doing some other operation on it. The table itself should look like the visual you produced. I just need it in form of a table.

[jdbuchanan71]

@slavisha84 

I don't think you do need a new table.  You want to be doing your operation using the date table and your revenue table as it is.  That way you can use time intelligence functions and slice the data by whatever other categorical date you have.  
Forcing your data into a new table that looks like the visual would make it less useful.  What if you want to summarize it by month or quarter instead of week?  With the model as is that is easy:

Here is a good free course on data modeling for PowerBI you might find helpful.  Getting you data model right from the beginning makes is easier to maintain, easier to extend and faster.
https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/

[slavisha84]

@jdbuchanan71 The reason why I need it in the real table is that I need to perform 4 Week Rolling Average on each of these Columns and then plot it on Line Chart. So it should be One Chart that will give me 3 lines. 3rd line would be Forecasting data. The reason why I need it in this format is that I can not plot 3 lines on that graph unless I get the transformed data into a new table and perform the calculations I mentioned. 

Please take a look at this thread at the bottom to see what issue I am trying to solve ultimately.
Solved: Re: How to perform 4 weeks rolling average for fis... - Microsoft Power BI Community
I really need a regular table if that can be created in a format I was looking for. 

[jdbuchanan71 · Accepted solution]

OK, you can calculate the yearly 4 week rolling average with your data as is:

This is the measure for 2020:

2020 4w ra = 
VAR _Weeks = 4
VAR _FiscalWeek = SELECTEDVALUE ( FiscalCalendar[FiscalWeek] )
RETURN
    IF (
        NOT ISBLANK ( _FiscalWeek ),
        DIVIDE (
            CALCULATE (
                [Revenue Amount],
                CALCULATETABLE (
                    DATESINPERIOD (
                        FiscalCalendar[Date],
                        MAX ( FiscalCalendar[Date] ),
                        - ( _Weeks * 7 ),
                        DAY
                    ),
                    FiscalCalendar[FiscalYear] = 2020
                ),
                VALUES ( FiscalCalendar[FiscalYear] )
            ),
            _Weeks
        )
    )

But, if you are dead set on having a new table you can create it using this code.

New Table = 
ADDCOLUMNS(
    SUMMARIZE(FiscalCalendar,FiscalCalendar[FiscalWeek]),
    "2020",CALCULATE(SUM(RevenueByDate[Revenue]),FiscalCalendar[FiscalYear] = 2020),
    "2021",CALCULATE(SUM(RevenueByDate[Revenue]),FiscalCalendar[FiscalYear] = 2021)
)

I have updated the attached file showing both solutions.

[slavisha84]

@jdbuchanan71 
Thank you This is exactly what i needed. And you made it very understandable. 🙂