cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
joshcomputer1
Helper V
Helper V

sumif with condition and date rnage

I have a dataset that I am trying to sum the [deviance] column so that it is a rolling total by day.  I need to filter by another column [open_tasks] so that only "open" tasks are included and that the deviance is only calculated if the date selected in the slicer is within the start_date to end_date range.  *I have a separate calendar table named [date].  Please look at the bottom chart for the desired result. 

 

 

ss1.GIF

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

Hi @joshcomputer1 ,

 

Please refer to below measure.

Devaince Measure =
CALCULATE (
    SUM ( Dataset[deviance] ),
    FILTER (
        Dataset,
        Dataset[start_date] <= MAX ( 'Date'[Date] )
            && Dataset[end_date] >= MAX ( 'Date'[Date] )
            && Dataset[open_tasks] = "open"
    )
)

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft
Microsoft
Microsoft

Hi @joshcomputer1 ,

 

Please refer to below measure.

Devaince Measure =
CALCULATE (
    SUM ( Dataset[deviance] ),
    FILTER (
        Dataset,
        Dataset[start_date] <= MAX ( 'Date'[Date] )
            && Dataset[end_date] >= MAX ( 'Date'[Date] )
            && Dataset[open_tasks] = "open"
    )
)

1.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LivioLanzo
Solution Sage
Solution Sage

Hello @joshcomputer1 

 

the trick here is to apply the below transformation within Power Query and then everything will become super easy:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQHQkslHSVjfWMDCMvQAEjkF6TmKcXqgBQYQ4SN9I1MoQpMgURyTn5xagpUiSnMDCNzqBJUI8zh8hYQlimqvCVUl74x1DWWSDbEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [start_date = _t, end_date = _t, deviance = _t, open_tasks = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"start_date", type date}, {"end_date", type date}, {"deviance", Int64.Type}, {"open_tasks", type text}}),
    AddedCustom = Table.AddColumn(ChangedType, "Custom", each List.Dates([start_date], Duration.Days([end_date] - [start_date]) + 1, #duration(1,0,0,0)), type list),
    ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
    ChangedType2 = Table.TransformColumnTypes(ExpandedCustom,{{"Custom", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(ChangedType2,{"Custom", "open_tasks", "deviance"})
in
    #"Removed Other Columns"

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors