Forum Discussion
Super UserHelp with DAX on DirectQuery
Hi there,
Need help with following scenario, I'm using Azure SQL Server with DirectQuery in PowerBI.
I have phone calls data table which is related to calendar table. Problem I'm trying to solve here is to compare number of calls on a date with average number of calls in previous days.
Here is what average mean:
I have a slicer table, allows you to select average for how many days? 7, 15, 30, 45, 60
I have a formula called "How many Average Days?" = if(HASONEVALUE(SlicerTable), VALUES(SlicerTable[Days]), 7), giving default value to 7days, if no value is selected in formula
So for example if we are looking at data as of March 03rd, so average will be number of calls between start date and yesterday, in this case start date will be Feb 24th (Yesterday - How many average days), in other words (March 02nd - 7 Days) and end date will be yesterday (March 02nd).
We will count all the calls between Feb 24th and March 02nd and then divide by How many average days?
In this whole thing I'm unable figure out how to get start date since DAX doesn't allows DATEADD functionality on directquery and I'm unable to figure out any other way. I hope it is detailed enough to provide help.
Thanks,
Parv
lls in a date range / number of days
6 Replies
- Greg_Deckler
Community Champion
Does the old ([date] + 7 * 1.) trick work? That would add 7 days to [date] for example.
- parry2k
I get this error when I'm trying to add following column in Calendar Table
Here is how "How Many Average Date?" measure is calculated in calendar table:
How many average days? = IF(HASONEVALUE('Days for Average'[Days]), MAX('Days for Average'[Days Value]), 30)- parry2k
Any help on this or alternate solution? Thanks!
