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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors