Forum Discussion
How to use dax to implement the list.date function in the M function, just like the question require
- 4 years ago
Yes I know it's fake data, I just wanted to know if the amounts were correct without having to calculate them (they're not).
OK, first create a Dates table which covers all the dates in the model (so from earliest award start to the latest award end).
Then, in the first table, create 2 columns. One for the number of days between start and end. The other for the amount per day (a simple division).
Then create a table with DAX:
GENERATE ( Table1, DATESBETWEEN ( Dates[Date], [Award start], [Award end] ) )That should do it.
Now, having said that, with your real data, you should consider a Power Query solution to get the data compression OR maybe use measures to produce any final visualisation because the model is going to get big very quickly.
Questions before I go further.
Are you saying you explicitly want a DAX solution rather than Power Query?
US or UK dates?
Are the values in the desired table accurate?
yes I want a dax solution
UK date enough
that this is fake data, and that the real data contains 19,000 grants some with a 30 year range