Forum Discussion

user01's avatar
user01
Resolver 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
Super User
1 year ago

Hi user01 ,
You are right, I missed that part. The formula below should work for all the cases.

Sum_Sales =

var _edselected = IF("E" in ALLSELECTED('Table'[PRODUCT]) || "D" in ALLSELECTED('Table'[PRODUCT]) ,

TRUE(),FALSE())

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

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

⭐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
Resolver I
1 year ago

Hi GrowthNatives 

 

Thanks, but it did not work for me. It's basically what I had before, which does not solve "Notice that Product E has no sales in JAN and FEB and shows up blank on the chart. Yes, this is desired." and "Desired behavior if D (and/or E) is selected, and P is selected, but SEP through DEC are deselected (meaning, when there is no data for D and/or E)" from my main post.