Show Prior Week Values together with Current Week Values

[francesca289]

Hey community! 

I have been going crazy about this issue for the whole day and would greatly appreciate your help. I am working with containers, each of which has loading date and an unloading date, see example below:

I want to see, week by week, the number of containers unloaded against the number of containers loaded during the immediately prior week, see concept explained below in Excel:

So that I can build a bar chart that looks like this, basing my x axis on the Week Unloaded:

I tried multiple approaches, I created a Prior Week measure and then calculated the number of containers loaded based on it, and I also tried CALCULATE ( DISTINCTCOUNT (table[Container ID], table[Week Loaded]= table[Week Unloaded]-1). I also added an ALL(table(Week Loaded) in the DAX expression above but I can't seem to get the same number of containers loaded that I have for each week, the value is usually lower than what it should be, or it stays the same for all weeks.

Your help is REALLY greatly appreciated! 

[francesca289]

Thank you so much Jihwan! It seems to be working great 👏

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I think it depends on how your calendar table looks like and how the relationship between the fact table and the calendar table looks like.

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file. I hope the below can provide some ideas on how to create a solution for your datamodel.

Unloaded container this week: = 
CALCULATE (
    COUNTROWS ( DISTINCT ( Data[Container ID] ) ),
    USERELATIONSHIP ( 'Calendar'[Date], Data[Unloaded On] )
)

Loaded container previous week: = 
VAR _currentyear =
    MAX ( 'Calendar'[Year] )
VAR _currentweek =
    MAX ( 'Calendar'[Week Number] )
VAR _yearcondition =
    IF ( _currentweek <> 1, _currentyear, _currentyear - 1 )
VAR _previousweek =
    IF (
        _currentweek <> 1,
        _currentweek - 1,
        MAXX (
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = _currentyear - 1 ),
            'Calendar'[Week Number]
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( DISTINCT ( Data[Container ID] ) ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Year] = _yearcondition
                && 'Calendar'[Week Number] = _previousweek
        )
    )