Forum Discussion
How to use dax to implement the list.date function in the M function, just like the question require
I've got data that relates to the amount of money given to research grants, which each have a start and end date.
I have a table that looks like this:
| ResearchGrant | Award total | Award start | Award end |
| Bridge design | £10000 | 01/01/2019 | 01/04/2019 |
| Count bees | £500000 | 01/02/2019 | 01/05/2020 |
| Predict the future | £5545345 | 04/05/2019 | 05/12/2020 |
I would like to pivot it to create a table with a column in a date format that shows the value available per day, for all days between the start and end of each grant. I'm imagining something like this:
| Date | ResearchGrant | Amount per day |
| 01/06/2019 | Bridge design | £ 13.00 |
| 01/06/2019 | Count bees | £ 0.52 |
| 01/06/2019 | Predict the future | £ 2,000.10 |
| 02/06/2019 | Bridge design | £ 13.00 |
| 02/06/2019 | Count bees | £ 0.52 |
With multiple rows per date, and per research grant.
I know how to use Power query or DAX to calculate the number of days between the dates, the amount per day, month etc. But I don't know how to generate that table with a long list of dates, as previewed in the second table above. I would also be happy if the calculation wasn't made per day but was by week or even by month.
I should mention that this is fake data, and that the real data contains 19,000 grants some with a 30 year range.
Any help is much appreciated
I want to know if it is possible to use GENERATE in DAX, but I don't know how to implement it?
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.
4 Replies
- HotChilli
Community Champion
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.
- HotChilli
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
v-yalanwu-msftCommunity Support
Hi, jhin ；
According to HotChilli’s solution, I made a slight modification. You can create a date table first, and then create another table.
1.create a date table.
Date = CALENDAR(MIN('Table'[Award start]),MAX('Table'[Award end]))2.create a another table.
table2 = VAR _a = ADDCOLUMNS ( GENERATE ( 'Table', DATESBETWEEN ( 'Date'[Date], [Award start], [Award end] ) ), "Amount per day", DIVIDE ( [Award total], DATEDIFF ( [Award start], [Award end], DAY ) ) ) RETURN SUMMARIZE ( _a, [ResearchGrant], [Date], [Amount per day] )The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
