Forum Discussion

Volvo_Chang's avatar
Volvo_Chang
Helper I
7 years ago

Measure: Values (Between Dates)

Hello commnunity,

 

I have a measure question regarding date period,  2019-07-05 11_04_25-Window.png

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 

Need Help
Tips and Tricks

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Volvo_Chang
      Helper I
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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