Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
@v-shex-msft 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)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.