Hello,
I have a entity (table) hosted on dataVerse that has a date column (date type) and more than 10k records.
In Power BI desktop, I have to relate/ transform the current month's records with last month's records.
The result I wanted would be a slicer with the current month and other with the previous month and being able to change the slicer options for the previous month to make other comparisons with other months.
Attempts:
1. I created a parameter for the current month and another for the previous month.
2. I created two tables to store the current month values and the previous month values and pointed out the respective parameters.
3. I imported the table with directQuery with the following condition:
Source = CommonDataService.Database("org.crm4.dynamics.com"),
DataVerseSQL = Value.NativeQuery(Source, "Select * from customEntity where Convert(varchar(10), custom_date, 103) = '" & Parameter1 & "'", null, [EnableFolding = true]),
This solution works, when changing the slicer value the parameter is updated and the query is executed changing the output and returning the records I selected.
Problem:
However, because it is a directQuery I cannot transform the data after doing the Value.NativeQuery.
Expected output:
Being able to filter the entity using a slicer and being able to transform the values too, because I need to join other tables and data.
Any help is very welcome!
Thank you,
Ruben
