1

I have two tables.
Fact:

Date Value
2021-10-21 1
2021-10-23 2
2021-10-26 3

Dim:

Date
2021-10-19
2021-10-20
2021-10-21
2021-10-22
2021-10-23
2021-10-24
2021-10-25
2021-10-26
2021-10-27
2021-10-28

fact_start_date = minx(all(Fact), Fact[Date])
fact_end_date = maxx(all(Fact), Fact[Date])

I want to write measure in Fact table that the result is the following table.
ِDim table Dates are between fact_start_date and fact_end_date of Fact table and empty values are replaced with zero.

notes:
1.write measure in Fact table
2.use measure, not new table

result:
(select Date from Dim)

Date measure
2021-10-21 1
2021-10-22 0
2021-10-23 2
2021-10-24 0
2021-10-25 0
2021-10-26 3

thanks

j.p
  • 96
  • 6
  • using measure (not calculated column) `SumOfValues = SUM('Fact'[Value])+0` and filtering the dates work fine? without filter it will show 0 for all values from Dim that are not in Fact, this can also be done via code if needed – Stachu Oct 29 '21 at 07:41
  • Thank you for answer. So right. The formula `IF(ISBLANK(SUM(FactFunds[Value])), 0, SUM(FactFunds[Value]))` can also zero the blanks. But I want to filter the dim date with measure automatically. – j.p Oct 29 '21 at 09:11

1 Answers1

0

I tried very much with Formula SUMX (VALUES(Dim[Date]), IF (Dim[Date] <= fact_end_date && Dim[Date] >= fact_start_date, SUM(Fact[Value]) + 0)) but did not receive the correct answer. The formula was correct. The problem was relationship(Cross filter direction) between Dim table and Fact table, which was Both. When I changed it to Single the formula was implemented correctly.

j.p
  • 96
  • 6