Forum Discussion

cskoglund's avatar
cskoglund
Frequent Visitor
9 years ago
Solved

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...
  • cskoglund's avatar
    cskoglund
    9 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

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.:smileyhappy:

 

Regards

cskoglund's avatar
cskoglund
Frequent Visitor
9 years ago

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