Sum Value if it meets 2 criteria/filters

[dla005]

Hi, 

Is there a DAX measure that if it meets the 2 filter restriction then to sum up the value column? Below is what my data looks like and the 2 restriction are, Table Recieved date > = Recieved Date and Table Recieved Date < Complete Date.  Table Recieved Date is basically Matrix visualizations with Recieved Date as the rows. I also already created a Date table using calendar auto and linking it to my query through Received Date.

My goal is to capture any inventory that is not completed by the end of the month. Thank you so much for your help!

Received Date	Complete date	Value
march	march	0
march	june	1
march	null	1
march	april	1
april	july	1
may	june	1
june	june	0
july	july	0

[dla005]

Hi, 

Is there a DAX measure that if it meets the 2 filter restriction then to sum up the value column? Below is what my data looks like and the 2 restriction are, Table Recieved date > = Recieved Date and Table Recieved Date < Complete Date.  Table Recieved Date is basically Matrix visualizations with Recieved Date as the rows. I also already created a Date table using calendar auto and linking it to my query through Received Date.

My goal is to capture any inventory that is not completed by the end of the month. Thank you so much for your help!

Received Date	Complete Date	Value
march	march	0
march	june	1
march	null	1
april	july	1
may	june	1
june	june	0
july	july	0

[MFelix]

Hi @dla005 ,

I'm having some confusion in understanding your first requirement:

Table Recieved date >= Recieved Date 

How can you have a line value tha the receveid date is higher than the received date? (I'm looking at indivual line values).

On the data below can you share what would be the calculation for the months?

Using the following measure:

Measure =
CALCULATE (
    SUM ( 'Table'[Value] );
    FILTER (
        ALL ( 'Table'[Complete Date] );
        'Table'[Complete Date] >= MAX ( CalendarTable[Date] )
            || 'Table'[Complete Date] = BLANK ()
    )
)

I'm getting this values:

March - 2

April -1

May - 1

Are this values correct?

[amitchandak]

@dla005 , refer this example between the start and end date, current employee

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[mahoneypat]

From your example data (which may be oversimplified), this expression should give you the # of rows that meet both criteria.

Rows Meeting Both =
COUNTROWS ( FILTER ( Table, Table[Complete date] <> Table[Received date] ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[dla005]

Hi @mahoneypat,

I tried using the DAX you recommended and it doesnt work because I think it doesnt take into account the value column? 

What i was hoping the DAX would return is the goal/correct value below. For example, May should return 4 because there was 6 "items" that was recieved in May and before (4 in Mar, 1 in Apr, and 1 in May) and 2 of which was completed before the end of May, so the total ending inventory for May that is still open is 4. If the item is recieved in May and compelted in May thata count as 0. (Complete date > Recieved Date).

Goal

Received Date	#
March	3
April	3
May	4
June	2
July

[mahoneypat · Accepted solution]

Thanks for clarifying.  I thought the Value column was to show which rows to keep.  In any case, here is an expression that gets your desired results.  FYI That I duplicated your Received Date column and made it into a Date column so this expression would work.

New Measure =
VAR __thisdate =
    MIN ( 'Table'[Received Date   ] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value  ] ),
        ALL ( 'Table' ),
        'Table'[Received Date   ] <= __thisdate,
        OR (
            'Table'[Complete date  ] > __thisdate,
            ISBLANK ( 'Table'[Complete date  ] )
        )
    )

If needed, here is the M code to transform your example data.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wyk0sSs5QUNKBMIC0gVKsDpJwVmleKpAyRBXNK83JQRUF8hILijIRojBeVmlOJZLSSnQzoRwoZQAVy6lEaAWKxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Received Date " = _t, #"Complete date " = _t, #"Value " = _t]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "Received Date ", "Received Date - Copy"),
#"Added Suffix" = Table.TransformColumns(#"Duplicated Column", {{"Received Date ", each _ & " 2020", type text}}),
#"Added Suffix1" = Table.TransformColumns(#"Added Suffix", {{"Complete date ", each _ & " 2020", type text}}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Suffix1",{{"Received Date ", type date}, {"Complete date ", type date}, {"Value ", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Complete date ", null}})
in
#"Replaced Errors"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[dla005]

Thank you @mahoneypat  what you provided works perfectly!! I been trying to figure this out for like a week! One quick question, why did you use Min('Table'[Received Date]) and why did you use the statement below? I would assume it should be an and statement? Again, Thank you so much for your help!

 OR (

            'Table'[Complete date  ] > __thisdate,

            ISBLANK ( 'Table'[Complete date  ] )

@amitchandak  Thank you for the suggestion. I will look over it as I am still learning Power BI so anything is helpful!