Hello,
This is my first post, so bear with me.
I've got a azure SQL database (connected with direct query as I want to publish to the service) with source data similar to this:
| Region | Month | sales | forecasted sales |
| EU | 01-01-2020 | 2689.541 | 2875.812 |
| US | 01-01-2020 | 5028.112 | 5401.079 |
| Asia | 01-01-2020 | 8516.668 | 8470.503 |
| EU | 01-02-2020 | 9076.184 | 8778.406 |
| US | 01-02-2020 | 4269.955 | 4103.235 |
| Asia | 01-02-2020 | 8849.809 | 8549.279 |
| EU | 01-03-2020 | 5163.491 | 4888.218 |
| US | 01-03-2020 | 683.7241 | 725.5368 |
| Asia | 01-03-2020 | 713.021 | 666.8129 |
| EU | 01-04-2020 | | 3696.9 |
| US | 01-04-2020 | | 3506.469 |
| Asia | 01-04-2020 | | 8734.434 |
I want to make a dashboard where I give the user a date slide and a region tickbox. After moving that around, he should be able to see the average sales and forecasted sales for all the selected regions and months.
I managed to do this fine with an imported dataset from excel, publishing to the service went fine too. Solved with a measuer like this AVERAGEX(SUMMARIZE(Table; Table[Date];"TempDate";sum(Table[ForecastSales]));[TempDate]) (one each for sales and forecasts and a bunch of other columns).
Now the source is being migrated to an Azure SQL database. If I imported the data, I could keep everything more or less the same as in the "excel version". But it seems as if that could not be published to the service (not updating from Azure SQL). Direct Query options allow updates.
I try the same measure in a direct query, it only includes the last date. I dont understand why. If I add the date filter (drag to the "Filters on this visual") and filter anything but the newest, I get "blank", and the average is only over the last date.
So there are two options to solve this
- I manage include data import from Azure SQL into PowerBi Service.
- Manage to make the measure run with the direct query.
Could someone please help.
