Forum Discussion

jhin's avatar
jhin
New Member
4 years ago
Solved

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 ...
  • HotChilli's avatar
    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.