Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
anttijarvinen
Regular Visitor

How to calculate sum of values by last date and distinct key

Hi!
I have a problem where I need to calculate sum (and average) of values, by only using the last applicable date (filtered by date-filter), and only using distinct keys related to the values.

Example of data:

KeyValueDate

a

11.1.2023
a22.1.2023
a33.1.2023
b310.2.2023
b220.2.2023
b110.5.2023
c55.2.2023
c56.2.2023
c523.5.2023

Dates can be very randomly distributed (based on modification of data).

 

For example if user filter date = 1.3.2023, applicable rows would be: (last rows before that point of time)

a33.1.2023
b220.2.2023
c5

6.2.2023

Which would return 8 for sum.

 

I couldn't find a case with such complexity, only simpler sumx distinct examples, but I also need to dynamically consider which is the last applicable date by that point of time.

 

Thank you

Antti Järvinen

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

hi @anttijarvinen 

not sure if i fully get you. try to

1) plot a slicer with a calculated table like:

dates = CALENDAR(MIN(data[Date]), MAX(data[Date]))

2) plot a measure like:

measure = 
VAR _table2 =
ADDCOLUMNS(
    VALUES(data[Key]),
    "Date",
    CALCULATE(
        MAX(data[date]),
        data[date]<=MAX(dates[date])
    )
)
RETURN
CALCULATE(
    SUM(data[value]),
    TREATAS(_table2, data[Key], data[date])
)

it worked like:

FreemanZ_0-1685152407500.png

Only a has a 3.

FreemanZ_2-1685152515935.png

a has 3, b as 2, c has 5

FreemanZ_3-1685152607923.png

 

 

 

View solution in original post

3 REPLIES 3
anttijarvinen
Regular Visitor

I noticed a problem when I have a more complicated situation, where I need to apply more filters than just the Date, and to do this inside RETURN-clause (exclude filters from _table2).

 

==> I was able to solve this by creating a new calculated column which tracked the next Date-value. Therefore I was able to specify the validity date-range for each row.

FreemanZ
Super User
Super User

hi @anttijarvinen 

not sure if i fully get you. try to

1) plot a slicer with a calculated table like:

dates = CALENDAR(MIN(data[Date]), MAX(data[Date]))

2) plot a measure like:

measure = 
VAR _table2 =
ADDCOLUMNS(
    VALUES(data[Key]),
    "Date",
    CALCULATE(
        MAX(data[date]),
        data[date]<=MAX(dates[date])
    )
)
RETURN
CALCULATE(
    SUM(data[value]),
    TREATAS(_table2, data[Key], data[date])
)

it worked like:

FreemanZ_0-1685152407500.png

Only a has a 3.

FreemanZ_2-1685152515935.png

a has 3, b as 2, c has 5

FreemanZ_3-1685152607923.png

 

 

 

Thank you, your idea works with small modifications to my use case, and therefore I accepted it as solution 🙂
(I didn't remember to specify in my original post that I need to plot the result of this function (y) against date (x), and so I further had to remove the relationship between data and calendar tables to make your solution work).
Otherwise, I think I would need to use e.g. ALL function somehow.. maybe you have an idea how to further modify the DAX-function to not be required to remove the relationship?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.