Forum Discussion

jhin's avatar
jhin
New Member
5 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 ...
Need Help
Tutorial Requests
  • HotChilli's avatar
    HotChilli
    5 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.

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
5 years ago

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.