The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
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