Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi All,
I have a requirement where I need to display past 30 days trend in Matrix visual based on selection done from date filter. For example, if 13th July is selected, then the Matrix should display dates from 13th July to 13th June in Descending order. Similarly, if 1st July is selected then Matrix should display dates from 1st July to 1st June.
Below is my sample dataset.
Below is the expected output :
I have tried below measure till now but no success.
Current Output irrespective of what date I select in filter.
Please help me out here @GilbertQ @amitchandak @Greg_Deckler
Solved! Go to Solution.
@sahildoshi I just realized what you actually want. PBIX file is attached below signature.
Selector =
VAR _sel = MAX('Calendar'[date])
VAR _min = _sel - 5
VAR _date = MAX('Transactions'[fact_date])
RETURN
IF(_date >= _min && _date <= _sel,SUM(Transactions[Sales]),BLANK())
@sahildoshi Maybe:
30 days Value =
var _sel = SELECTEDVALUE('date'[date])
var _mindate = _selected - 5
return SUMX(FILTER(ALL('fact'),[fact_date] <= _sel && 'fact'[fact_date] > _mindate),'fact'[Sales])
Hi @Greg_Deckler , thank you for your response. I tried your measure but it just sums up sales of period in consideration. Please refer below snapshot when date selected is 12 Jul. It sums up sales from 9 Jul to 12 Jul and displays for all dates.
@sahildoshi I just realized what you actually want. PBIX file is attached below signature.
Selector =
VAR _sel = MAX('Calendar'[date])
VAR _min = _sel - 5
VAR _date = MAX('Transactions'[fact_date])
RETURN
IF(_date >= _min && _date <= _sel,SUM(Transactions[Sales]),BLANK())
Hi @Greg_Deckler , thank you your solution it works. So the key part was the relationship between the tables, in my case the fact and calendar was connected. I'll have to create a copy of transaction table (disconnected table) for this visual then.
@sahildoshi So where is the Date coming from in that table visual? From Date or Fact table? Also, is there a relationship between those two tables? If you can post the sample data you provided as text, I can mock up a quick PBIX and get it solved.
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
90 | |
83 | |
75 | |
49 |
User | Count |
---|---|
145 | |
140 | |
109 | |
68 | |
55 |