cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cskoglund
Frequent Visitor

Calculate with visual filter looses date context

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!

1 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

View solution in original post

2 REPLIES 2
v-ljerr-msft
Microsoft
Microsoft

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.Smiley Happy

 

Regards

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.