previous day cost measure based on selection of bar chart

[Shruthi96]

Hi Folks,

I've been tasked by the team to develop a functionality that retrieves data from the previous day based on the selected bar on the X-axis of the chart, which represents dates.

For instance, suppose I've applied a date filter selecting non-consecutive dates such as March 22nd, 25th, and 27th. If the user selects the bar representing March 27th on the chart, I need to create a measure that calculates the cost based on the data from the previous day displayed on the chart, which in this case would be March 25th.

I have a measure in place to retrieve data for the day prior, specifically for March 26th. However, I need to dynamically calculate the previous day based on the user's selection on the chart's X-axis display. For example, if the user selects the bar representing March 27th on the chart, I need the measure to fetch data from March 25th. Thanks in advance

[v-tangjie-msft · Accepted solution]

Hi @Shruthi96 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

Measure 2 = 
var _date1=CALCULATE(MAX('Table'[Date]),ALLSELECTED('Table'))
var _date2=MAXX(FILTER(ALLSELECTED('Table'),[Date]<_date1),[Date])
RETURN CALCULATE(SUM('Table'[cost]),FILTER(ALL('Table'),[Date]=_date2))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Shruthi96]

Thanks for taking time and replying. 

The requirement is when i select multiple dates, (consecutive or non consecutive dates) from the drop down date filter,. Later select the bar chart, example when i select 26th March.. my previous day is 24th March.  There is no pattern for date selection, users should have flexibility to choose the dates based on the requirements.

[Shruthi96]

@v-tangjie-msft , let me know if you know the solution for my requirement. 

[v-tangjie-msft]

Hi @Shruthi96 ,

Unfortunately, due to the design of PBI Desktop, the behavior of clicking on 3-26 only filters data from 3-26, and there is no associated DAX function to get the date you clicked to select, you can refer to the fifth reply to use a slicer instead.

If you would like to suggest  feature improvements, you can post your idea to Ideas.  https://ideas.powerbi.com/ideas/

Best Regards,

Neeko Tang

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

[_AAndrade]

Can you share pbix to take a look?

[_AAndrade]

Hi @Shruthi96, in your bar chart you don't have consecutive days because you didn't have cost on those days, am I right?
If it's the case you can add an IF clause on your measure to find the day before when the "measure you are using on the bar chart" is <> to zero.

Did you get the idea?

[Shruthi96]

Hi, I have values for all the days, but need to analyse on displayed dates on chart.