Hello All,
I have the Fact Table Data in the below format.
I created a Date Table
Date Table = calendar(DATE(2020,04,01),DATE(2023,12,31))
And Relationship is like
Active Relationship between Fact[Mat Date] - Date Table[Date]
InActive Relationship between Fact[Avail Date]- Date Table[Date]
My scenario is
I have Start Date (01-04-2022) and End Date(30-09-2022) as date Filter from Date Table and
I will be getting 4 rows with my selection, to get Yellow Highlighted row(5 th row)
I created a measure using the DAX
Availed Date =
var a = CALCULATE(MAX(Fact[Avail Date]),USERELATIONSHIP(Fact[Avail Date],Date Table[Date]))
var b = MAX(Fact[Avail Date])
return
IF(ISBLANK(a),b,a)
I’m getting correct result with the above DAX.
The same logic for Amt
Amt =
var a = CALCULATE(SUM(Fact[Amt]),USERELATIONSHIP(Fact[Avail Date],Date Table[Date]))
var b = SUM(Fact[Amt])
return
IF(ISBLANK(a),b,a)
I should get the Total 40833.6 but I’m getting 29,166.69 (Total- It’s excluding the first row i.e, 01-03-2022).
SUMX = SUMX(VALUES(Dim[ID]),[Amt]) This is giving 35000.06 as Total but Last row is Blank.
Here’s the Link for the pbix File
Wrong Totals with Userelationship.pbix (60.2 KB)
Can anyone suggest the alternative for this!!!
TIA