Sum based on expression

[mstefancik]

Hello, could anybody help me to rewrie SQL command to DAX. I woud like to sum column from table invoice based on two dates columns difference and divide the results into bins, based on dates difference.

SQL query:

SELECT
sum(CASE WHEN Date2-Date1 < 7 THEN value END) AS '0-7',
sum(CASE WHEN Date2-Date1 < 14 THEN value END) AS '7-14',
sum(CASE WHEN Date2-Date1 < 30 THEN value END) AS '14-30',
sum(CASE WHEN Date2-Date1 > 30 THEN value END) AS '>30'
FROM invoice

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @mstefancik,

In your scenario, you can create a calculated column to display days differences, then create another column to display values based on this difference column. After that, you can place those columns in a matrix visual. Please refer to screenshots below:

Date2-Date1 = DATEDIFF(Table2[Date1],Table2[Date2],DAY)

Column = Switch(true(),Table2[Date2-Date1]>30,">30",Table2[Date2-Date1]<=30 && Table2[Date2-Date1]>14,"14-30",Table2[Date2-Date1]<=14 && Table2[Date2-Date1]>7,"7-14",Table2[Date2-Date1]<=7,"0-7",BLANK())

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[mstefancik]

@v-qiuyu-msft thank you very much, it works like a charm!