Forum Discussion
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. 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 |
- 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?
4 Replies
- Hi there
Thanks for the details, what is your expected output that you are looking for?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!- 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?
