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
lw24
Helper I
Helper I

Sum of Distinct columns by Created Previous Month Date

Hello all! 

 

Here's the data I'm trying to calc the previous months (February) data based on these distinct columns: 

 

lw24_0-1678485842798.png

 

Thank you!!

1 ACCEPTED SOLUTION

Thank you, gave that one a shot but wasn't quite what I was looking for. I'd been working on this seperately with this formula and got to what I needed, not sure if this is efficient?...

 

New Column = 

CALCULATE(

SUMX(

DISTINCT (Table'[ID]), CALCULATE(MAX('Table'[Amount]))),

DATESINPERIOD('Table'[Created Date],MAX('Table'[Created Date]),-30,DAY))

 

 

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @lw24 

please try

NewColumn =
VAR T1 =
CALCULATETABLE (
'Table',
ALLEXCEPT (
'Table',
'Table'[File Date],
'Table'[Name],
'Table'[ID],
'Table'[Created Date]
)
)
VAR T2 =
FILTER (
T1,
FORMAT ( 'Table'[File Date], "YYMM" ) = FORMAT ( 'Table'[Created Date], "YYMM" )
)
RETURN
MAXX ( T, 'Table'[Amount] ) + 0

Thank you, gave that one a shot but wasn't quite what I was looking for. I'd been working on this seperately with this formula and got to what I needed, not sure if this is efficient?...

 

New Column = 

CALCULATE(

SUMX(

DISTINCT (Table'[ID]), CALCULATE(MAX('Table'[Amount]))),

DATESINPERIOD('Table'[Created Date],MAX('Table'[Created Date]),-30,DAY))

 

 

FreemanZ
Super User
Super User

hi @lw24 

the logic is not very clear. could you elaborate it?

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