Forum Discussion

richard_jackson's avatar
richard_jackson
Frequent Visitor
2 years ago

Show data only for last week when multiple tables have data

Hi there!

I have multiple data sources with dates and a value. I wish to have a report which displays data only for the last calendar (/ISO) week that all data sources have at least one value.

 

Models:
- 'DataTable01', with [Date] and [Amount]

- 'DataTable02', with [Date] and [Amount]

- 'CalendarTable', with [Date] and [IsoWeek]

 

Relationships:

- 'CalendarTable'[Date] 1 -> * 'DataTable01'[Date]

- 'CalendarTable'[Date] 1 -> * 'DataTable02'[Date]

 

Approach

1. Create calculated columns on CalendarTable which identify whether any date exists on the DataTables for those rows:

 
DateExistsInDataTable01 =
IF (
    COUNTROWS (RELATEDTABLE (DataTable01)) > 0,
    True,
    False
)
DateExistsInDataTable02 = 
IF (
    COUNTROWS (RELATEDTABLE (DataTable02)) > 0,
    True,
    False
)

...then one column to just check whether data exists in all tables:

DataExistsInAllDataTables = 
    AND([DateExistsInDataTable01],
        [DateExistsInDataTable02])

 

2. Create a measure which identifies the Latest Iso Week

LatestIsoWeek = 
VAR LatestDate =
    CALCULATE (
        MAX ( 'CalendarTable'[Date] ),
        CalendarTable[DataExistsInAllDataTables] = TRUE)
RETURN
    CALCULATE (
        VALUES ( 'CalendarTable'[IsoWeek] ),
        CalendarTable[Date] = LatestDate)

 

...and this is where I'm stuck. I want to then filter the visuals in Report view to only show the [Amount] and [Date] values where `CalendarTable`[IsoWeek] is equal to the measure [LatestIsoWeek].

 

Any assistance or direction would be greatly appreciated.

 

Thanks!

 

Rich

 

 

 

 

Need Help

2 Replies

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi richard_jackson I would merge multiple transaction tables into one summarized table by grouping Sum(Amont) by Date and then create a Calendar table with one to many relationship to the summarized table. I can then use the Date column from the Calendar table as slicer with Relative Date option to filter results by last 1 week, month etc. 

    Hope this helps. 

  • richard_jackson's avatar
    richard_jackson
    Frequent Visitor
    2 years ago

    Hi amustafa - thanks for this!

    The problem here is that I need to check that all the data tables contain data for at least one day of the week we're reporting on - if 'DataTable01' contains data for weeks 1-5, but 'DataTable02' contains data only for weeks 1-3, then the report needs to show data for week 3 only (as week 3 is the most recent week where data is present for both tables).

     

    Using Relative Date will only provide the previous week/month etc. regardless of whether data tables contain data for for that week/month etc.

     

    Any advice or guidance would be greatly appreciated.

     

    Thanks!

     

    Rich