Help creating a 6-Week Rolling 'Matrix Table' using multiple measures

[Anonymous]

Hi All,

I have been tasked with replicating an old Excel table into our new PowerBI reporting

Below is an example of what the table looks like:

I've tried following a few examples I've found on these forum's and at this stage I havent been able to get anything working.

We already have most of the values populating cards as per the below:

And I need to take those values and put them into the table above for each week ending sunday night.

The first, third, fifth and sixth cards above are just showing 'Counts' and cards two and four are based on SUMX measures.

Any help, ideas or guidance is very much apprecaited 😃

[amitchandak]

Rolling  in case you have date table ,

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-6*7,DAY))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak - Thanks for your response but I'm not sure how to use this in the context of the table I'm trying to build?

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Also, show the desired result.

[Anonymous]

@Ashish_Mathur - The desired result would hopefully look like the table in the beginning of my post. I've been able to replicate other tables from Excel, I've just not done a 6 week rolling one in PowerBI before.

As for sample data, would pasting the the tables into Excel with some explanation be enough?

[Ashish_Mathur]

Hi,

If in the Calendar Table, you can also share a Week number column, then i may be able to help.