'SUMMARIZE - calculate difference between column value and variable in DAX
I'm trying to write a measure which multiplicate number of days and value for period from slicer with date (e.g. 01/05/2022-08/05/2022). I have 2 tables - Table and tblDate.
My basic Table is something like that:
Date Value DateStart DateEnd
24/04/2022 24 24/04/2022 27/04/2022
27/04/2022 24 27/04/2022 28/04/2022
28/04/2022 24 28/04/2022 02/05/2022
02/05/2022 23 02/05/2022 06/05/2022
06/05/2022 20 06/05/2022 31/12/2022
There is relation between Date column and tblDate.
I wrote a measure:
Measure =
var xDateEnd = MAX('tblDate'[Date])
var xDateStart = MIN('tblDate'[Date])
var tempTable = CALCULATETABLE(
SUMMARIZE('Table',"value", sum('Table'[value])
,"no of days", Calculate(MIN('Table'[DateEnd]-1,xEndDate))-Calculate(MAX('Table'[DateStart],[xStartDate])+1))),'Table'[DateStart]<=xDateEnd, 'Table'[DateEnd]>xDateStart, ALL('tblDate')
)
RETURN sumx(tempTable,[value]*[no of days])
But I have error Calculate(MIN('Table'[DateEnd]-1,xEndDate))-Calculate(MAX('Table'[DateStart],[xStartDate])+1))) - I cannot compare date from column with variable.
Result which I would like to have for period 01/05/2022-08/05/2022 is 176 (124 + 4 * 23 + 320) :
Date Value DateStart DateEnd no of days
24/04/2022 24 24/04/2022 27/04/2022
27/04/2022 24 27/04/2022 28/04/2022
28/04/2022 24 28/04/2022 02/05/2022 1
02/05/2022 23 02/05/2022 06/05/2022 4
06/05/2022 20 06/05/2022 31/12/2022 3
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
