Relationship Needed?

I don't have anything like that littlemojopuppy , going to read about it now!

Hi Anonymous , I took what littlemojopuppy suggested, which someone in my organisation also suggested, but I couldn't get it to work how I wanted. I have also been through those links and can't see my exact problem.

I'll try and suggest more because I have possibly over complicated a lot, which has made a fairly simple solution difficult.

Let's start with Query 1. That is a long list of invoices - I'll show column headers so there is no sensitive data.

I have about 200k invoices a week and data goes back to the start of 2020. Ignoring most of what the columns represent, the date last paid is the important one.

WEEKNUM doesn't work here properly as there are two years of data. So I have columns for WEEKNUM and YEAR, then I CONCATENATE them year first to get YYYYWW. 

To compund this I want an overall page that always looks at the current week and year. To do that I had to get creative. 

Current Week = IF(Query1[Week(no format)]=MAX(Query1[MWW]),"Current", CONVERT(Query1[Week(no format)],STRING))

 

MWW = MAX(Query1[MW])

MW = VALUE(IF(VALUE(LEFT(Query1[Concat],4))=Query1[MY],VALUE(RIGHT(Query1[Concat],2)),"0"))

Concat = CONCATENATE(Query1[Year],Query1[Week])

MY = max(Query1[Year])

Week = FORMAT((WEEKNUM(Query1[Date Last Paid].[Date])-1),"00")

Week(no format) = WEEKNUM(Query1[Date Last Paid].[Date])-1

Year = YEAR(Query1[Date Last Paid].[Date])

 

The -1 in the week calcs is just because of the way my organisation counts financial weeks. A similar form is used for Current Year.

 

There is probably a more elegant solution, but that's what I got to. 

 

This allows me leave slicers on the front page that are set to Current Week and Year, but also the option to look at previous weeks.

 

Then we have Query 2. This is obviously a different table. It is a list of suppliers, with a date for creation, a disabled column, and a last updated column. In a given week, we can assume a supplier has been closed on that date it was last updated if it has also got a Y in disabled.

 

Because of all these factors, I have reproduced what I did for Query 1 for both creations and closures, ensuring their slicers don't effect each other.

 

This has left me with what I have in the orginal picture:

 

What I'm looking at doing is having the slicers at the top also affecting the creations and closures:

 

 

Given the manner I have approached all of this, I'm wondering if it even possible without going back to formula?

 

If not I am probably going to move the suppliers set up off the front page to leave room for something else.