Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a simple table visual showing a list of transactions, each row of which has a date and a value (a dollar amount). I have a slicer which allows the user to select a date, and the table correctly shows only the transactions for that date.
I now have a requirement to show the Year To Date figures - so whatever date the user chooses in the slicer, he'll see a separate display of the total of the transactions this year up to and including the chosen date.
How can I calculate the total of the transactions up to and including the selected date? I created a simple calculation:
This Year NB To Date = sumx(filter(Tx, Tx[Created On] <= [Date Selected] && Tx[Created On] >= DATE(2018,1,1)), Tx[Value])
Note that the "[Date Selected]" item is just a variable holding the date selected in the slicer.
But the slicer stops that calculation from seeing all the dates - it just gives the total for the date selected in the slicer, not for the whole year up to and including that point.
Hi @Budfudder
You may create a calendar table and use it as slicer. Make sure there is no relationship between it and Tx table as below. Then you may get the value with your measure as requested.
Date Selected = SELECTEDVALUE('Calendar'[Date])
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |