Hello,
I have a simple Sales table:
SalesDate, Brand, Product code, Product descr, Total sales, Total sales LY
SalesDate has relationship with a “CALENDAR” table, and I’m using ‘Dates’[Date] to ensure continuity.
These are 2 measures:
Total sales = SUM(‘Net Sales’[Invoiced sales])
Total sales LY =
CALCULATE(SUMX(‘Net Sales’;‘Net Sales’[Invoiced sales]);SAMEPERIODLASTYEAR(Dates[Date]))
Same problem with this variant:
Total sales LY =
CALCULATE(SUM(‘Net Sales’[Invoiced sales]);SAMEPERIODLASTYEAR(Dates[Date]))
I want to create a virtual table like this:
Table PE =
SUMMARIZE(
‘Net Sales’;
‘Net Sales’[Brand];‘Net Sales’[Product];‘Net Sales’[Product descr];“Total Invoiced sales”;[Total sales]; “Total invoiced sales LY”;[Total sales LY]
)
The aim is to have the “Total sales LY” and “Total invoiced sales LY” as a static column.
I do not understand the reason why ‘Table PE’[Total invoiced sales LY] is always blank.
Does the summarize function doesn’t accept calculated time intelligent expressions?
No problem with the other column “Total Invoiced sales”…
Hope you can help me, I’m stucked. It seems to be an easy thing but I’m not able to get the virtual table working as expected.
Thanks
For the best assistance I would suggest adding some images and also a demo file.
The issue could be multiple things but I’m confident the answer is actually quite simple. But without all the details it’s difficult to give a solid answer.
Also see here for details on time intelligence and virtual tables. Maybe this will clear some details up for you in the meantime.
Also it looks like you may be using SUMX unnecessarily here when a simple SUM would be fine, or just using the measure branching methodology.
Hi
Here attached you can find the simple pbx. I made some progress with your tips, but still not achieved my goal.
Based on the date filter (that, at present, affects the NetSales tab visual only) I want also drive the results of the virtual table.
I need “Total invoiced sales” and “Total invoiced sales LY” as static columns.
I also think/hope I’m “drowning in a glass of water”. Hope it will be then easy to help me changing a little my file. See also the picture.Thanks in advance. TestVirtualTables.pbix (103.9 KB)
If I may, you just need to add the link in the model to the virtual table, same as done with NetSales (link the Date to the needed date), so you can filter same way
If the goal here is the creation of the two calculated columns, why not create them in your Net Sales table, rather than creating new tables? Once you’ve got the calculated columns added to Net Sales, you can organize/summarize them any way you want in the visuals. This will keep both your data model and your DAX simpler.
In addition, I noticed in your PBIX file that Dates was not marked as a date table. You should make sure to do that in order to ensure that your time intelligence functions calculate properly.
Hi BrianJ,
unfortunately this is not an option. I cannot provide you the whole concept here, I think it would neither be beneficial. I need a static table, that’s my goal. Of course, what you say is what I alredy get with the first table in the page, but this is not my need. Thanks so far.
In addition, I noticed in your PBIX file that Dates was not marked as a date table.
Not sure here. I don’t know the way how I could mark a table this way. The field Dates[Date] is data type Date.
Thanks so far.
KR
@richard_d
Thanks for the tip, I applied the suggested changes but the problem is still the same. Here attached you can find the latest pbix version.
What I’d like to underline is that if I use the time intelligence dax formula as a measure it does work. The problem is the virtual table. As soon as I add the Date field to it (and I filter for a specific period/year) the virtual table is not “able” anymore to calculate the past (probably because the values are not available anymore due to the filter that affects the data behind and not only the visual). This is the main problem I guess. KR
Hi @Ferraglia, we’ve noticed that no response has been received from you since January 23, 2020. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
Unfortunately none of the advised solutions fit my needs. As I underlined since the beginning I need a static table as result. It seems to be not an easy thing…
Hi @Ferraglia, we’ve noticed that no response has been received from you since January 28, 2020. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
To the contributors of this post, thank you for all your inputs on this topic we are now tagging it as Solved. To help us learn more about your experience in the forum, please take a moment to answer this short **forum survey**. We appreciate all your help and suggestions. Thanks!
