DAX help for dates

[kata]

Hi All,

I have a fact table with 2 dates and an amount in it: Date A (day level) and Date B (day level). I have also a Date table, with days in it, and it has active connection with the fact table on Date A.

The aim is to have a hierarchical slicer from Date table, so have year, month and day in it, and I would like to see on a visual on monthly level how much sum amount I have, using 2 conditions: I do not want to consider the amount where Date B = Date from Date table, and I want to consider the amount where Date A> Date from Date table.

In case for example if someone is filtering on 2nd October I would like to see the amount for October where Date B is not equal with 2nd Oct, and the amount where Date A is between 3rd October and 31st October.

Can you please help me here how to write this DAX?

Thank you in advance for all your support

[lbendlin]

You need to use measures as visual filters.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[kata]

Hi,

Thank you for the information and answer. Please see below a table as an example, please let me know if you need more information.

I have a fact table holding information about Archivierungsdatum, Wareneingangsdatum and EKW netto. For that table I have a Date table connected on the Archivierungsdatum.

I would like to have a table visual for example, where next to the date from my Date table there is the sum of the EKW netto with the following logic implemented:

Next to the date 3 rd of Oct (let's take that date as an example), I can find the sum of the EKW netto, where Archivierungsdatum is equal or bigger than the Date (than 3rd October) - so sum of the netto where Archivierungsdatum is 3rd October and 4th October in our example -, and Wareneingangsdatum is smaller than the Date (so in our example smaller than 3rd October).

So based on our table the sum I would like to see for 3rd Oct is 14.349.448,43.

One more information: I have a big fact table so when I have tried (unsuccesfully) to use SUMX and FILTER in another measure it caused some performance problem:(

Thank you again for all of your support/answer and wishing you a nice evening/day.

Archivierungsdatum	Wareneingangsdatum	EKW Netto
02.10.2023 00:00	29.03.2023 00:00	€ 22.796,40
02.10.2023 00:00	03.04.2023 00:00	-€ 1,84
02.10.2023 00:00	05.06.2023 00:00	€ 16,32
02.10.2023 00:00	01.10.2023 00:00	€ 339.762,40
02.10.2023 00:00	02.10.2023 00:00	€ 224.586,47
02.10.2023 00:00	03.10.2023 00:00	€ 8.636,70
03.10.2023 00:00	12.05.2023 00:00	€ 76,80
03.10.2023 00:00	13.05.2023 00:00	€ 16,50
03.10.2023 00:00	03.10.2023 00:00	€ 127.800,48
03.10.2023 00:00	04.10.2023 00:00	€ 2.183,50
04.10.2023 00:00	27.09.2023 00:00	€ 3.328.090,14
04.10.2023 00:00	28.09.2023 00:00	€ 3.857.418,78
04.10.2023 00:00	29.09.2023 00:00	€ 3.927.561,41
04.10.2023 00:00	30.09.2023 00:00	€ 288.206,96
04.10.2023 00:00	01.10.2023 00:00	€ 362.508,71
04.10.2023 00:00	02.10.2023 00:00	€ 2.585.569,13
04.10.2023 00:00	03.10.2023 00:00	€ 2.057.837,86
04.10.2023 00:00	04.10.2023 00:00	€ 108.484,84