Measure

[Volvo_Chang]

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. However, I have using the measure as below but get the net cases combined instead of separated view as table1. Can you please help how to adjust my measure to get it to work? Thanks.

NetCasesRFP = calculate(sum('summary InvoiceDCByDay'[NetCases]),filter('Pricing',average('summary InvoiceDCByDay'[InvoiceDate])>=Pricing[Valid After] && average('summary InvoiceDCByDay'[InvoiceDate])<=Pricing[Valid Before]))

Table 1

Net Cases	Price Start Date	Price End Date
100	4/1/2019	5/1/2019
108	5/1/2019	6/1/2019
5	6/1/2019	7/1/2019

Table now

Hello Team,

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. However, I have using the measure as below but get the net cases combined instead of separated view in the table. Can you please help how to adjust my measure to get it to work? Thanks.

My measure for this one now:

NetCasesRFP = calculate(sum('summary InvoiceDCByDay'[NetCases]),filter('Pricing',average('summary InvoiceDCByDay'[InvoiceDate])>=Pricing[Valid After] && average('summary InvoiceDCByDay'[InvoiceDate])<=Pricing[Valid Before]))

Net Cases	Price Start Date	Price End Date
	4/1/2019	5/1/2019
213	5/1/2019	6/1/2019
	6/1/2019	7/1/2019

Measure 

[GilbertQ]

Hi there

Thanks for the details, what is your expected output that you are looking for?

[Volvo_Chang]

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.

NetCases1	Price Start Date	Price End Date
213	4/1/2019	5/1/2019
213	5/1/2019	6/1/2019
213	6/1/2019	7/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: 

NetCases1	Price Start Date	Price End Date
100	4/1/2019	5/1/2019
108	5/1/2019	6/1/2019
5	6/1/2019	7/1/2019

 Thank you very much!

[GilbertQ · Accepted solution]

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]

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