I am trying to get the cumulative values based on the selected Month & Year. The aim of the calculation is to show all cumulative months from the selected month. i.e. when Sep 16 is selected from the slicer, I would like to see Jan-Sep 16 values. Have tried a few attempts with no luck.
The below DAX works fine if no selection is made, but once I select a month, it limits the table to that month only (which is the normal behaviour of a slicer). Have tried to use ALL with the Dates[Date], but again with no luck!
Hi @Hesham. Sounds like a disconnected date table might work. If you use it for your date slicer, then harvest the selected value, then use it to calculate your cumulative sales, perhaps?
Greg
Hi @Greg, thanks for sharing your thought. I will give that a try, but initially, I was trying to see if there is another way around it without adding a disconnected data table.
I think my challenge is that I have a date slicer already on the report page that is performing a filter context and if I add a new slicer, it means it will have to re-tweak all measures to consider that new slicer…so in short, more work for me!
Hesham
@AntrikshSharma Out of interest, would a VIRTUAL date table that ignores the date slicer work? I appreciate the current way is to use a disconnected date table, but maybe the virtual date table would solve the issue? I could be wrong … but it was an idea!
A virtual table is exactly what it says ‘virtual’ - that means you can’t visualize attributes from a virtual table because it only exist in memory during the execution of the measure. Furthermore a measure needs to return a scalar value so without an attribute to visualize the result against how would you know what that value represents?
Just some more words to say there’s no way around a disconnected date table, in scenarios like these
I hope this is helpful.
A virtual table is exactly what it says ‘virtual’ - that means you can’t visualize attributes from a virtual table because it only exist in memory during the execution of the measure. Furthermore a measure needs to return a scalar value so without an attribute to visualize the result against how would you know what that value represents?
I agree 98.2% with this statement. The one weird little loophole is the CONCATENATEX/UNICHAR(10) combination, which while technically returning a scalar mimics being able to materialize a column within a virtual table with context. I still think the disconnected date table is the way to go here, but that combo might be worth examining as a possible alternative.
