Measure: Values (Between Dates)

[Volvo_Chang]

Hello commnunity,

I have a measure question regarding date period,  

In the measure, it works perfect if I have two date period that one is after and the other one is before. 

Adjusted Cases = CALCULATE (
SUM ( 'summary InvoiceDCByDay'[NetCases]),
FILTER (
ALL ( 'summary InvoiceDCByDay'[InvoiceDate],'summary InvoiceDCByDay'[Brand],'summary InvoiceDCByDay'[Supplier],'summary InvoiceDCByDay'[Distribution Center] ),
('summary InvoiceDCByDay'[Distribution Center]) = max(Pricing[DCLocationName]) && ('summary InvoiceDCByDay'[Supplier]) = MAX(Pricing[MainItemSupplierName])
&& ('summary InvoiceDCByDay'[brand]) = max(Pricing[Brand])
&& ('summary InvoiceDCByDay'[InvoiceDate]) >= ([ValidAfterSelected])
&& ('summary InvoiceDCByDay'[InvoiceDate]) <= ([ValidBeforeSelected]))) + 0

Now, I want to just adjust the measure above and disreagard the validafter period and change the valid before from the "before" date slicer to "between" date slicer. 

How can I addjust my measure above based on the valid after "between" slicer?

Thanks,

Kevin 

[Anonymous]

HI @Volvo_Chang ,

You can use variables to store selected date records, then use minx,maxx to extract correspond values:

Adjusted Cases =
VAR selected =
    ALLSELECTED ( Calendar[Date] )
VAR name =
    MAX ( Pricing[DCLocationName] )
VAR supplier =
    MAX ( Pricing[MainItemSupplierName] )
VAR brand =
    MAX ( Pricing[Brand] )
RETURN
    CALCULATE (
        SUM ( 'summary InvoiceDCByDay'[NetCases] ),
        FILTER (
            ALLEXCEPT (
                'summary InvoiceDCByDay',
                [InvoiceDate],
                [Brand],
                [Supplier],
                [Distribution Center]
            ),
            'summary InvoiceDCByDay'[Distribution Center] = name
                && 'summary InvoiceDCByDay'[Supplier] = supplier
                && 'summary InvoiceDCByDay'[brand] = brand
                && 'summary InvoiceDCByDay'[InvoiceDate] >= MINX ( selected, [Date] )
                && 'summary InvoiceDCByDay'[InvoiceDate] <= MAXX ( selected, [Date] )
        )
    ) + 0

Regards,

Xiaoxin Sheng

[Volvo_Chang]

@Anonymous  Hello Mr. Sheng, Thanks for your reply.

After I enter your formula, it says a single value for Column Distribution Center in table summary InvoiceDCByDay can't be determined.  The Valid Before Date Slicer I am using is based on DAX=max(A Dates, B Dates, C Dates)

This shows Distribution Center in Table Summary IncoiceDCByday cannot be determined

[Anonymous]

HI @Volvo_Chang ,

Can you please provide a sample pbix file with some sample data for test?(you can upload to onedrive or google then share link here)
Notice: do mask on sensitive data before share.

Regards,

Xiaoxin Sheng