Power Pivot Measure with multiple date criteria identical year

[Lars Ole Sørensen]

Hi,

I want to make a measure that sum the column "kvantum" based on date criteria columns "landingsdato" and "salgsdato". When I in the timeline slicer choose the year 2018, the measure should show "kvantum" values when the criteria for "landingsdato" and "salgsdato" is in the year 2018.

My measure show a wrong value when i choose the year 2018 in the timeline slicer. I get the value 1.108.733 when the correct value is 1.107.738. The error is that i get a value from a row that contain "landingsdato = 31.07.2018" and "salgsdato=29.01.2019". This row should not be in the sum. The measure shows the correct value when I choose the year 2019 in the timeline slicer.

I hope someone can help me with this.
Thanks in advance.

Regards
Lars Ole

 

[Chihiro]

You really shouldn't have multiple date relationships from Kalender to Data_Marsluttsdl.

One way to deal with this, is to build 3 separate Kalender dimension for each of 3 columns. Then filter each for 2018.

See the model structure.


Resulting slicer setup.



Alternate method is to restructure your fact table. To track each event as separate line item (unpivot date columns and have event type as attribute).

Another alternative is to use... calculated column for filtering purpose. Though I'm not fan of this approach in DAX model.

Edit: Yet another approach is to use Crossfilter/USERRELATIONSHIP. However, if you are using more than one USERRELATIONSHIP, this can quickly get out of hand and isn't recommended approach in data modeling.

You can read about USERRELATIONSHIP in link below.
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

 

[Lars Ole Sørensen]

Chihiro, thank you very much for the fast and good help on this, it solved my problem