Forum Discussion

kata's avatar
kata
Frequent Visitor
2 years ago

DAX help for dates

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

 

Need Help

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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-Forum/ba-p/963216

    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/1447523

    • kata's avatar
      kata
      Frequent Visitor
      2 years ago

      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.

       

      ArchivierungsdatumWareneingangsdatumEKW Netto
      02.10.2023 00:0029.03.2023 00:00€ 22.796,40
      02.10.2023 00:0003.04.2023 00:00-€ 1,84
      02.10.2023 00:0005.06.2023 00:00€ 16,32
      02.10.2023 00:0001.10.2023 00:00€ 339.762,40
      02.10.2023 00:0002.10.2023 00:00€ 224.586,47
      02.10.2023 00:0003.10.2023 00:00€ 8.636,70
      03.10.2023 00:0012.05.2023 00:00€ 76,80
      03.10.2023 00:0013.05.2023 00:00€ 16,50
      03.10.2023 00:0003.10.2023 00:00€ 127.800,48
      03.10.2023 00:0004.10.2023 00:00€ 2.183,50
      04.10.2023 00:0027.09.2023 00:00€ 3.328.090,14
      04.10.2023 00:0028.09.2023 00:00€ 3.857.418,78
      04.10.2023 00:0029.09.2023 00:00€ 3.927.561,41
      04.10.2023 00:0030.09.2023 00:00€ 288.206,96
      04.10.2023 00:0001.10.2023 00:00€ 362.508,71
      04.10.2023 00:0002.10.2023 00:00€ 2.585.569,13
      04.10.2023 00:0003.10.2023 00:00€ 2.057.837,86
      04.10.2023 00:0004.10.2023 00:00€ 108.484,84