Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Shruthi96
Helper III
Helper III

previous day cost measure based on selection of bar chart

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

 

Shruthi96_0-1712078943336.png

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @Shruthi96 ,

 

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

(1) This is my test data. 

vtangjiemsft_0-1712308879084.png

(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.

vtangjiemsft_1-1712308913712.png

 

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. 

View solution in original post

7 REPLIES 7
v-tangjie-msft
Community Support
Community Support

Hi @Shruthi96 ,

 

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

(1) This is my test data. 

vtangjiemsft_0-1712308879084.png

(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.

vtangjiemsft_1-1712308913712.png

 

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. 

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_0-1713266832945.png

 

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

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
Super User
Super User

Can you share pbix to take a look?





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




_AAndrade
Super User
Super User

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?





Did I answer your question? Mark my post as a solution! Kudos are welcome.

Proud to be a Super User!




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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.