Forum Discussion
Measure: Values (Between Dates)
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 Cas...
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
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
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