Forum Discussion

user01's avatar
user01
Icon for Resolver I rankResolver I
1 year ago
Solved

Dynamic starting date based on filter for bar chart

I get data via DirectQuery and we publish our report our Power BI app, but I provided some dummy data below. We have Product P sales data starting May 2024. We have Product D and Product E sales data...
Need Help
  • user01's avatar
    user01
    1 year ago

    I re-did my approach. I made one measure to indicate whether or not E or D is selected by counting the E/D rows by using SUMX. Then I wrote another measure based on the first one.

    EDRows = SUMX(ALLSELECTED('Table'), IF('Table'[PRODUCT] in {"E", "D"}, 1, 0))

sum_salesx = 
var _ed = [EDRows2] > 0
var _eddate = IF(_ed, "202409", "202405")
return SUMX('Table',IF('Table'[YRMO] >= _eddate, 'Table'[SALES] + 0, BLANK()))

     

    I put sum_salesx as the Y-axis in the bar chart and it seems to work fine. And in the X-axis, "Show items with no data" is not selected.

GrowthNatives's avatar
GrowthNatives
Icon for Super User rankSuper User
1 year ago

Hi user01 ,
I understand you are having an issue with dynamically starting date based on filter for bar chart. You are on the right track, if you change your formula it should work.

Sum_Sales = 
var _edselected = IF("P" in ALLSELECTED('Table'[PRODUCT]),
FALSE(),TRUE())

var _eddate = IF(_edselected, "202409", "202405")

RETURN CALCULATE(SUMX('Table', 'Table'[SALES]), 'Table'[YRMO] >= INT(_eddate)+0)

 

Here is the .pbix file Dynamic Starting Date 


⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore more] 

user01's avatar
user01
Icon for Resolver I rankResolver I
1 year ago

Hi GrowthNatives,

 

Thank you, but this did not work. If (example) D and P are selected (your third image), the chart should start in SEP (max of (D start date, P start date)). Or, if D or E selected (P selected or unselected), the bar chart should start in SEP.

 

Desired result (example, D and E and P selected)