Forum Discussion

francesca289's avatar
francesca289
New Member
2 years ago
Solved

Show Prior Week Values together with Current Week Values

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! 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    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
        )
    )

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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
        )
    )