Wrong calculation using date and text filter

[esbertl]

I have a small sample project using this csv data ("testvalues.csv")

DateChanged,Value,Project
2024-01-01 00:00:00,100,Project A
2024-01-01 00:00:00,20,Project B
2024-01-10 00:00:00,-10,Project A
2024-01-11 00:00:00,10,Project A
2024-01-15 00:00:00,10,Project A
2024-01-15 00:00:00,5,Project B
2024-01-20 00:00:00,5,Project B
2024-01-25 00:00:00,10,Project A
2024-01-25 00:00:00,10,Project B

And I have added a mesure calulating the sums for every day:

ValueOnDate = CALCULATE(SUM(testvalues[Value]), FILTER ( ALL( testvalues[DateChanged] ), testvalues[DateChanged] <= MAX ( testvalues[DateChanged] )))

I can now visualize this data and even filter it by Project and it works just fine.

If I just filter "Project A" it looks like this:

If I filter "Project B" all correct values are calculated correctly as well:

And if I don't filter for projects or set both projects or if i choose "select all" everything works fine:

The calculated values are:

But if I add an additional filter on the date (even without filtering data) things are getting confusing:

If I now filter for both projects (without selecting "Select all") all rows are wrong having just a value for one project on a day:

Do you have a solution for me?

[esbertl]

Hi Wilson!

Works great! Thanks a lot!

[Wilson_]

Awesome, thanks for the update esbertl. Happy I could help! 😄

[esbertl]

Thank you Wilson for the quick answer!

In fact, with the help of the table, the strange calculation behavior is gone, unless I use the date table in the filter.

The basic idea of the filter on the date is to display only a certain section of the data. However, if I now select 02.01. as the start date, the values before 01.01. are missing from the total. I assume that I have to adjust my measure. Do you have any hints as to how the measure should look?

[Wilson_ · Accepted solution]

Hi esbertl,

The references to a date column in your measure should now reference the new date dimension table. 🙂

ValueOnDate = 
CALCULATE (
    SUM ( testvalues[Value] ), 
    FILTER ( 
        ALL ( 'Calendar'[Date] ), 
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[Wilson_]

Hi esbertl,

I strongly encourage you to build out a separate date table instead of having just one data table. Your problem goes away if you do.

Doing so is a best practice and you're seeing one of the reasons why. You can sometimes have unintended filtering side effects otherwise.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.