Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I am trying to calculate the number of days a campaign stayed in each status. With my current formula, if a campaign id has multiple entries on the same day, it counts days in status twice, rather than a 0. I have highlighted a few examples.
This is the current formula I am using with [days since] being # of days since the campaign was created.
Days in status = [days since]-LOOKUPVALUE(campaigns_logs[days since],campaigns_logs[campaign.id],[campaign.id],campaigns_logs[change_date].[Date],CALCULATE(MAX(campaigns_logs[change_date].[Date]),FILTER(campaigns_logs,campaigns_logs[campaign.id]=EARLIER(campaigns_logs[campaign.id])&&campaigns_logs[change_date]<EARLIER(campaigns_logs[change_date]))))
Solved! Go to Solution.
Hi @snortham ,
Here I suggest you to add an index column by group of [campaign.id] and [change_date].
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE3sVTSUTIyMDLSNzTUNwKyHZNLMstSdX2AhFKsDlYlnnkKAUX5yanFxdhUmGBTYWqIpMLQBKs9mGowLTJDVmJGWIkFYZuMDAkaYwTytUd+Toquc05mal4JVjXG2AMPRY0FdnOMkdVYYjcHQw0x5mB6C6HESN8UTUUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [compaign.id = _t, change_date = _t, #"previous_value - key" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"compaign.id", Int64.Type}, {"change_date", type date}, {"previous_value - key", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"compaign.id", "campaign.id"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"campaign.id", "change_date"}, {{"Rows", each _, type table [campaign.id=nullable number, change_date=nullable date, #"previous_value - key"=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"previous_value - key", "Index"}, {"Custom.previous_value - key", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom.previous_value - key", "previous_value - key"}, {"Custom.Index", "Index"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Index", Int64.Type}})
in
#"Changed Type1"
For refernece: Create Row Number for Each Group in Power BI using Power Query
New table looks like as below.
Then update your calculated column code as below.
Days in status =
IF (
campaigns_logs[Index] = 1,
[days since]
- LOOKUPVALUE (
campaigns_logs[days since],
campaigns_logs[campaign.id], [campaign.id],
campaigns_logs[change_date].[Date],
CALCULATE (
MAX ( campaigns_logs[change_date].[Date] ),
FILTER (
campaigns_logs,
campaigns_logs[campaign.id] = EARLIER ( campaigns_logs[campaign.id] )
&& campaigns_logs[change_date] < EARLIER ( campaigns_logs[change_date] )
)
)
),
0
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi All,
I'm looking for a support.
I want to calculate number of days between first and last date in the same column where I'm having multiple same ID entries with multiple dates
Can I get support with the DAX function logic? 🙏
Hi @snortham ,
Here I suggest you to add an index column by group of [campaign.id] and [change_date].
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE3sVTSUTIyMDLSNzTUNwKyHZNLMstSdX2AhFKsDlYlnnkKAUX5yanFxdhUmGBTYWqIpMLQBKs9mGowLTJDVmJGWIkFYZuMDAkaYwTytUd+Toquc05mal4JVjXG2AMPRY0FdnOMkdVYYjcHQw0x5mB6C6HESN8UTUUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [compaign.id = _t, change_date = _t, #"previous_value - key" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"compaign.id", Int64.Type}, {"change_date", type date}, {"previous_value - key", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"compaign.id", "campaign.id"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"campaign.id", "change_date"}, {{"Rows", each _, type table [campaign.id=nullable number, change_date=nullable date, #"previous_value - key"=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Rows],"Index",1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"previous_value - key", "Index"}, {"Custom.previous_value - key", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Rows"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom.previous_value - key", "previous_value - key"}, {"Custom.Index", "Index"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Index", Int64.Type}})
in
#"Changed Type1"
For refernece: Create Row Number for Each Group in Power BI using Power Query
New table looks like as below.
Then update your calculated column code as below.
Days in status =
IF (
campaigns_logs[Index] = 1,
[days since]
- LOOKUPVALUE (
campaigns_logs[days since],
campaigns_logs[campaign.id], [campaign.id],
campaigns_logs[change_date].[Date],
CALCULATE (
MAX ( campaigns_logs[change_date].[Date] ),
FILTER (
campaigns_logs,
campaigns_logs[campaign.id] = EARLIER ( campaigns_logs[campaign.id] )
&& campaigns_logs[change_date] < EARLIER ( campaigns_logs[change_date] )
)
)
),
0
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @snortham ,
You can use SUMX to virtually remove the duplicates within a measure.
=
SUMX (
// create a virtual table of unique rows of campaign id, status and days in status
SUMMARIZE (
'Table',
'Table'[Campaign ID],
'Table'[Status],
'Table'[Days in status]
),
'Table'[Days in status]
)
=SUMX(VALUES(
Proud to be a Super User!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
93 | |
90 | |
35 | |
35 |
User | Count |
---|---|
153 | |
99 | |
82 | |
63 | |
54 |