Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
70 | |
38 | |
27 | |
26 |
User | Count |
---|---|
97 | |
96 | |
59 | |
44 | |
40 |