Forum Discussion

Volvo_Chang's avatar
Volvo_Chang
Helper I
7 years ago
Solved

Measure

Hello Team, Need your expertise! I have net cases number pulled from daily invoice database. I want to show the net cases in the table is as the table below as separate view based on the date range...
Dataflow
  • GilbertQ's avatar
    GilbertQ
    7 years ago
    Hi there

    Could you not create a date table. and then link your data based on the dates of the transaction.

    In your Date table you could have a column with the Start and End Dates.

    Then your measure would just be the sum('summary InvoiceDCByDay'[NetCases]

    And you would use the column from your date table?
Volvo_Chang's avatar
Volvo_Chang
Helper I
7 years ago

Hello GilbertQ ,

 

Thanks for your reply,

I create the measure below to show the net cases by the date range I select:

NetCases1 = calculate(sum('summary InvoiceDCByDay'[NetCases]),filter('Pricing',AVERAGE('summary InvoiceDCByDay'[InvoiceDate])>=Pricing[Valid After] && average('summary InvoiceDCByDay'[InvoiceDate])<=Pricing[Valid Before]))
 
but it shows combined net cases as below: I don't want to show it all combined.

NetCases1Price Start DatePrice End Date
2134/1/20195/1/2019
2135/1/20196/1/2019
2136/1/20197/1/2019

 

 

I have two date sliicer, one is valid after to get the max day of Price Start Date, second one is Valid before to get the min of price End date.

The NetCase1 number is from the invoice date in between of the price start date and price end date.

*I am looking for a measure to show the net cases row by row by the day range I select like this below: 

NetCases1Price Start DatePrice End Date
1004/1/20195/1/2019
1085/1/20196/1/2019
56/1/20197/1/2019

 

 Thank you very much!
GilbertQ's avatar
GilbertQ
Super User
7 years ago
Hi there

Could you not create a date table. and then link your data based on the dates of the transaction.

In your Date table you could have a column with the Start and End Dates.

Then your measure would just be the sum('summary InvoiceDCByDay'[NetCases]

And you would use the column from your date table?
  • Volvo_Chang's avatar
    Volvo_Chang
    Helper I
    7 years ago

    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