Calculate with visual filter looses date context

[cskoglund]

Hi

I'm trying to create a running total to be evaluated for a specific period. The measure is applied in a stacked chart which has YYYYMM as x axis where continous x axis is disabled. The formula is based on a date table and a transaction table. It works until I apply a legend, or categorization, by location from the transaction table. Then the date table is disregarded and powerbi chooses the date column in the transaction table as a date table, which is unfortunate as it's incomplete.

E.g. filtering for all dates from last date in november - 14 days suddenly becomes last transaction date for location -14 days once location is added as a filter.

Does anyone have any idea why this happens and what I can do with it?

E.g.

EVALUATE
ROW (
    "gfdsfg", 
      CALCULATE (
        CALCULATE (
            LASTDATE ( L_Datetable[DateKey] ),
            FILTER (
                ALL ( L_Datetable ),
                L_Datetable[DateKey]
                    > ( MAX ( L_Datetable[DateKey] ) - 14 )
                    && L_Datetable[DateKey] <= MAX ( L_Datetable[DateKey] )
            )
        ),
        L_Datetable[YearMonth] = 201611,
        S_BSAK[Location] = "1"
    )
)

yields the last transaction for location 1, 01.11.2016,  (DD.MM.YYYY) where the outer calculate represent the filter applied in the chart as legend. It actually uses the daterange 01.11.2016 to 01.11.2016-14d instead of using the asked 15.11.2016 to 15.11.2016-14d range. Hence it ends up with 01.11.2016

While mashing it all in one calculate works as expected:

EVALUATE
ROW (
    "gfdsfg"; 
    
        CALCULATE (
            LASTDATE ( L_Datetable[DateKey] );
            FILTER (
                ALL ( L_Datetable );
                L_Datetable[DateKey]
                    > ( MAX ( L_Datetable[DateKey] ) - 14 )
                    && L_Datetable[DateKey] <= MAX ( L_Datetable[DateKey] )
            )
        ;
        L_Datetable[YearMonth] = 201611;
        S_BSAK[Location] = "1")
    
)

Which returns blank (i.e. no transactions as the max date in the date table is 15.11.2016-14d=01.11.2016, and hence no transactions fits as the first one is 01.11.2016, and the filter is bigger than)

Any tips and tricks are much appreciated!

[v-ljerr-msft]

Hi @cskoglund,

Could you post some sample data and the measures you are using in this case? It is better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[cskoglund · Accepted solution]

Hi

Thanks for helping me out. After pulling out all my hair over the problem I figured out that it was a silly error.

MAX ( L_Datetable[DateKey]) pulls the latest date from the filtered dataset as expected, but as I wanted to look at a defined time period independent of the transaction tables the latest transaction date didn't suit the purpose. I ended up calculating the end of month date by wrapping the function in a eomonth function (eomonth(MAX ( L_Datetable[DateKey]);0)-14)  which  means the filtered timeperiod will be correct irrespectable of the date filter added to the chart.

The only caveat from the solution is that for filtered periods without any transactions it will go haywire by using eomonth(blank). I expect it to either return an error or calculate the value of timecode zero. Guess I will have to sleep on that one. 

Edit: turning of biderectional filter propagation in the relationship between the fact and date table, and dropping the eomonth function resulted in the desired result.

Regards

Christian