Power Pivot: get value for Previous Non-Consecutive Date

[KeepTrying]

Hi Guys,

I have an issue which seems to be easy but it's DAX so it isn't

I have similar Table (called "Data") as source:

Added Table to data model then inserted a Power Pivot: count values in column "C" per Date and per Country:

I started to get proper values for previous date but couldn't do it properly.
You can see that in Pivot between row 8 and 12: previous date values are correct only for 2022.04.19 and 2022.05.09 but incorrect for the rest:

My DAX formula for Power Pivot (between row 8 and 12):

=var currentcountry = IF(HASONEVALUE(Data[Country]),VALUES(Data[Country]),BLANK())
var currentdate = MAXX(
FILTER(ALL(Data),Data[ExtractDate]= MAX(Data[ExtractDate]) && Data[Country]=currentcountry),Data[ExtractDate])
var previousdate = MAXX(
FILTER(ALL(Data),Data[ExtractDate]<currentdate && Data[Country]=currentcountry),Data[ExtractDate])
return
CALCULATE([Count of Status_Groupped_measure],FILTER(ALL(Data[ExtractDate]),Data[ExtractDate]=previousdate), Data[Country]=currentcountry)

For DAX about you'll need "Count of Status_Groupped_measure":
=COUNT(Data[Status_Groupped])

I found that missing data is a problem: e.g. there is no value for HU for 2022.04.24 in source so cell is empty in Pivot. I assume these missing data in source table leads to incorrect values in Power Pivot (between row 8 and 12 on last picture) - but can't fix it.

Links I used:
Comparing with previous selected time period in DAX - SQLBI
Growth from Previous Non Consecutive Date - DAX Challenge - Goodly

Thanks for your advice/help in advance.

Kind Regards,

KeepTrying

 

[excel_learnerz]

It looks like the issue you're running into is related to missing values in the source table, which is causing the DAX formula to return incorrect results for certain dates and countries.

One approach to address this issue is to use a "virtual table" that includes all the possible combinations of dates and countries, and then use the SUMX function to iterate over this table and calculate the count of status grouped measure for each combination.

Here's an example of how you could modify your DAX formula to use this approach:

=VAR VirtualTable =
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES(Data[ExtractDate]),
            VALUES(Data[Country])
        ),
        "CurrentDate", [ExtractDate],
        "CurrentCountry", [Country],
        "PreviousDate", CALCULATE(
                            MAX(Data[ExtractDate]),
                            FILTER(
                                Data,
                                Data[ExtractDate] < [ExtractDate] &&
                                Data[Country] = [Country]
                            )
                        )
    )
RETURN
    SUMX(
        VirtualTable,
        VAR PrevCount = 
            CALCULATE(
                [Count of Status_Groupped_measure],
                FILTER(
                    Data,
                    Data[ExtractDate] = [PreviousDate] &&
                    Data[Country] = [CurrentCountry]
                )
            )
        RETURN
            IF(
                [CurrentDate] = MAX(Data[ExtractDate]),
                [Count of Status_Groupped_measure],
                PrevCount
            )
    )

In this formula, the VirtualTable variable creates a "virtual" table that includes all the possible combinations of dates and countries, along with the previous date for each combination. The SUMX function then iterates over this table and calculates the count of status grouped measure for each combination.

The IF statement within the SUMX function checks if the current date is equal to the maximum date in the source table, and if so, returns the count of status grouped measure for the current date and country. Otherwise, it returns the count of status grouped measure for the previous date and current country.

This approach should handle missing values in the source table and ensure that the formula returns the correct results for all dates and countries.

 

[RoryA]

I would imagine you could fix it by just not including the country filter in your previousdate section since you want the previous date regardless of whether it applied to that country:

var previousdate = MAXX(
FILTER(ALL(Data),Data[ExtractDate]<currentdate),Data[ExtractDate])

 

[KeepTrying]

It looks like the issue you're running into is related to missing values in the source table, which is causing the DAX formula to return incorrect results for certain dates and countries.

One approach to address this issue is to use a "virtual table" that includes all the possible combinations of dates and countries, and then use the SUMX function to iterate over this table and calculate the count of status grouped measure for each combination.

Here's an example of how you could modify your DAX formula to use this approach:

=VAR VirtualTable =
    ADDCOLUMNS(
        CROSSJOIN(
            VALUES(Data[ExtractDate]),
            VALUES(Data[Country])
        ),
        "CurrentDate", [ExtractDate],
        "CurrentCountry", [Country],
        "PreviousDate", CALCULATE(
                            MAX(Data[ExtractDate]),
                            FILTER(
                                Data,
                                Data[ExtractDate] < [ExtractDate] &&
                                Data[Country] = [Country]
                            )
                        )
    )
RETURN
    SUMX(
        VirtualTable,
        VAR PrevCount =
            CALCULATE(
                [Count of Status_Groupped_measure],
                FILTER(
                    Data,
                    Data[ExtractDate] = [PreviousDate] &&
                    Data[Country] = [CurrentCountry]
                )
            )
        RETURN
            IF(
                [CurrentDate] = MAX(Data[ExtractDate]),
                [Count of Status_Groupped_measure],
                PrevCount
            )
    )

In this formula, the VirtualTable variable creates a "virtual" table that includes all the possible combinations of dates and countries, along with the previous date for each combination. The SUMX function then iterates over this table and calculates the count of status grouped measure for each combination.

The IF statement within the SUMX function checks if the current date is equal to the maximum date in the source table, and if so, returns the count of status grouped measure for the current date and country. Otherwise, it returns the count of status grouped measure for the previous date and current country.

This approach should handle missing values in the source table and ensure that the formula returns the correct results for all dates and countries.

Dear excel_learnerz,

Thanks for your time and effort. Your solution is way above my DAX knowledge
I tried to use your DAX formula and unfortunately it gives the same result as original Power Pivot:

My desired result (prepared manually): get value for previous date:

I attach the sample file, your DAX is included in Pivot between row 10 and 15 and called "Count of Status_Groupped_PrevDateValue_measure_01". Link:
PowerPivot_PreviousNon ConsecutiveDate_Test_02.xlsx

Thanks a lot.

 

[KeepTrying]

I would imagine you could fix it by just not including the country filter in your previousdate section since you want the previous date regardless of whether it applied to that country:

var previousdate = MAXX(
FILTER(ALL(Data),Data[ExtractDate]<currentdate),Data[ExtractDate])

Dear RoryA,
Thanks for the hint but it doesn't work. I get this:

In previous post I shared link for the file which includes my original solution + excel_learnerz's solution.

Thanks again for your time.