I'm struggling trying to find a measure that can do the following:
For example, assuming the data begins January 1, 2020. If Nov 30, 2020 is selected from the date slicer, the corrresponding visual (bar chart with dates as x-axis) should show Nov 25, 26, 27, 28, 29, 30 along the x-axis while the counts for each date should equal to the amounts from Jan 1, 2020 to Nov 25, Jan 1 to Nov 26...
Unforunately, every combination I try results in:
If someone could help provide a solution, i'd be extremely greatful. Thank you!
Solved! Go to Solution.
Hi @AccrualJoke ,
You can do this by creating a disconnected date table for the picker (this means it's not connected by relationship to another table in the model). You can do this with the TABLE DAX:
Then you would NOT use the date picker date on your chart, the other one.
Hi @AccrualJoke ,
You can do this by creating a disconnected date table for the picker (this means it's not connected by relationship to another table in the model). You can do this with the TABLE DAX:
Then you would NOT use the date picker date on your chart, the other one.
@DataZoe Thank you so much! I just tried your measure now and it's working well!
The one issue is that I'll need to calculate totals beyond just the year. The data will most likely spanning past 2020 into future periods. So is there a variation that allows for that?
I tried substituting TOTALYTD with Calculate, but that doesn't seem to work.
Please let me know if you have further insight to this! Thank you again!
The TOTALYTD will work on any period, so should accomodate future years just like it is.
Is there a way to make it so that the cumulative count does NOT reset at the beginning of a new year?
For example, instead of a TOTALYTD is there an equivalent for life-to-date or inception-to-date? I would want 2021 to include the 2020 and prior year counts as part of the 2021 totals.
@AccrualJoke Sure, you can use this cumulative instead:
Thanks again for saving me!
Instead of referencing the Date column from my disconnected Date Table, I used the date column from my actual data table. After making the switch, the measure seemed to work perfectly fine.
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
User | Count |
---|---|
431 | |
189 | |
106 | |
56 | |
49 |
User | Count |
---|---|
463 | |
222 | |
127 | |
74 | |
70 |