Forum Discussion

DPH72's avatar
DPH72
Regular Visitor
4 years ago
Solved

Remove filter on one column while retaining filter on another

I have table containing daily volumes by country and date. Each country has a different end date. I want to calculate the sum of the volumes that each country has sold in the past 7 days, using the last 7 days on record for each country. 


The problem I am running into is that my measures count back from the last date in the date set, rather than the last date for each country. As a result, the total comes out too low as not all countries have a full 7 days of data.  

 

For example, if the last date with volume information for country A is 2022-07-12, and the last date with volume information for country B is 2022-07-14, I want to calculate the sum of volume in country A between 2022-07-06 and 2022-07-12 + volume in country B between 2022-07-08 and 2022-07-14. 

All the approaches I have tried return the total volume for all countries counting back from the overall end date in the dataset. Continuing with the example, this would be the total volume for all countries between 2022-07-08 and 2022-07-14. However, because country A does have have volume data on record for 2022-07-13 or 2022-07-14, the total sum is lower that it should be. 

 

Desired

CountryStart DateEnd DateDaily VolumeVolume in Past 7 DaysTotal Volume in Past 7 Days, All Countries
A2022-07-062022-07-12     3     21     56
B2022-07-082022-07-14     5     35     56

 

What I'm getting

CountryStart DateEnd DateDaily VolumeVolume in Past 7 DaysTotal Volume in Past 7 Days, All Countries
A2022-07-062022-07-12     3     21     50
B2022-07-082022-07-14     5     35     50

 

The total volume in the past 7 days should be 56, but because two days of data are missing for country A, it only contributes 15 to the total volume instead of 21, because it only has 5 days of data available in the period of 2022-07-08 and 2022-07-14. 

Here is the DAX that I am using to calculate the total for each country in the past 7 days (this returns the desired value): 

TotVol_L7D =
CALCULATE(
    SUM('00All'[DailyVolTonnes]),
    '00All'[Manufacturer] = "Total",
    '00All'[Date] > Max('00All'[Date]) -  6,
)

Here is the DAX that I am using to calculate the sum for all countries (this returns a sum that is to low, because it is using the same date range for all countries) 

CALCULATE(
    '00All'[TotVol_L7D],
    ALL('Country')
)

I've tried variations on this theme but I keep getting the same result. I believe that I need a more nuanced approach to my filtering but I'm not sure how to achieve it. 
Need Help
  • jgeddes's avatar
    jgeddes
    4 years ago

    To replicate my understanding of your concern I created the dataset

    I then wrote a measure for lastSevenDaysVolume

    LastSevenDaysVolume =
    SUMX(
        SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_sum", CALCULATE(SUM(lastSevenDays[Volume]),lastSevenDays[Date] > datevalue(MAX(lastSevenDays[Date])-7))),
        [_sum]
    )
    and a measure for Total Volume
    TotalVolumeL7D =
    CALCULATE(
        SUMX(
            SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_volume", [LastSevenDaysVolume]),
            [_volume]
        ),
        ALL(lastSevenDays[Country])
    )
    with the resulting table

    This may not be the exact solution to your concern but it should point you in the right direction.

4 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    4 years ago

    To replicate my understanding of your concern I created the dataset

    I then wrote a measure for lastSevenDaysVolume

    LastSevenDaysVolume =
    SUMX(
        SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_sum", CALCULATE(SUM(lastSevenDays[Volume]),lastSevenDays[Date] > datevalue(MAX(lastSevenDays[Date])-7))),
        [_sum]
    )
    and a measure for Total Volume
    TotalVolumeL7D =
    CALCULATE(
        SUMX(
            SUMMARIZE(lastSevenDays,lastSevenDays[Country], "_volume", [LastSevenDaysVolume]),
            [_volume]
        ),
        ALL(lastSevenDays[Country])
    )
    with the resulting table

    This may not be the exact solution to your concern but it should point you in the right direction.

  • Arul's avatar
    Arul
    Super User
    4 years ago

    DPH72 ,

    try to use ALLEXCEPT,

     

    TotVol_L7D =
CALCULATE(
    SUM('00All'[DailyVolTonnes]),
    '00All'[Manufacturer] = "Total",
    '00All'[Date] > Max('00All'[Date]) -  6,
    ALLEXCEPT('00All','00All'[Country])
)

     

    Thanks,

    Arul

    • DPH72's avatar
      DPH72
      Regular Visitor
      4 years ago

      Hi Arul

      Thanks for your reply. Unfortunately this measure calculates the total volume for all countries counting back from each country's individual end date, rather than first calculating the volume in the last 7 days on a by-country basis, and then calculating the sum of these country-specific volumes. Luckily jgeddes was able to provide the desired solution.