Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculated Column or Measure to be used with 2 date range slicers all from 1 data source

Hi Everyone - I'm relatively new to the Power BI tool.  I have created a couple of calculated fields that are not correct when adjusting date sliders (via a slicer visualization).  When I leave the full date range in the data set (i.e. I do not adjust the date sliders), the calculations work as expected. However, if I change the data range on either or both sliders, the calculations do not adjust.

 

This report is connected to just 1 data source which is a query imported using the  Advanced Options and writing a SQL statement.  The data contains 1.8MM records with about 20 columns and this is updated daily with records from the most recent day's transactions.  2 of the columns included in the data set are 'Effective Date' and 'Creation Date'. 

 

End users would like to change the date slider for either/both date column (these are imported directly from my sql query) and my calculated columns should adjust to calculate solely on the filtered data (using the 2 date sliders).

 

Here is one of the formulas: 

Grand Total by ID & Currency = CALCULATE(Sum(Table[Amount]),ALLEXCEPT(Table,Table[Currency],Table[ID]))

 

I tried adding the 2 date fields into the 'AllExcept' logic but that really goofs things up.  I would greatly appreciate any assistance in getting this to work.  I'm new to Power BI but not other reporting tools.

Thanks!

Need Help

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    Anonymous wrote:

    Hi Everyone - I'm relatively new to the Power BI tool.  I have created a couple of calculated fields that are not correct when adjusting date sliders (via a slicer visualization).  When I leave the full date range in the data set (i.e. I do not adjust the date sliders), the calculations work as expected. However, if I change the data range on either or both sliders, the calculations do not adjust.

     

    This report is connected to just 1 data source which is a query imported using the  Advanced Options and writing a SQL statement.  The data contains 1.8MM records with about 20 columns and this is updated daily with records from the most recent day's transactions.  2 of the columns included in the data set are 'Effective Date' and 'Creation Date'. 

     

    End users would like to change the date slider for either/both date column (these are imported directly from my sql query) and my calculated columns should adjust to calculate solely on the filtered data (using the 2 date sliders).

     

    Here is one of the formulas: 

    Grand Total by ID & Currency = CALCULATE(Sum(Table[Amount]),ALLEXCEPT(Table,Table[Currency],Table[ID]))

     

    I tried adding the 2 date fields into the 'AllExcept' logic but that really goofs things up.  I would greatly appreciate any assistance in getting this to work.  I'm new to Power BI but not other reporting tools.

    Thanks!

    Could you share some sample data and clarify more details about your expected result?

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.