Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a base table as shown below in Power BI.
Using that I need to create two tables in Power BI
1. Sold Products
2. Unsold Products
These two tables should be filtered based on the 'Date' field.
I need a DAX formula to satisfy the 3 cases shown in the image below to get the desired output in 2 different (SOLD & UNSOLD) tables for all the 3 cases.
Data Link: Here
Solved! Go to Solution.
Hi @ketan10,
Suppose your base table is called 'Base Table'. Create a calendar table.
dim date = CALENDAR ( MIN ( 'Base Table'[Date] ), MAX ( 'Base Table'[Date] ) )
In 'Base Table', create measures like below:
Total = CALCULATE ( SUM ( 'Base Table'[Total Qty Sold] ), FILTER ( 'Base Table', 'Base Table'[Date] >= MIN ( 'dim date'[Date] ) && 'Base Table'[Date] <= MAX ( 'dim date'[Date] ) ) )
outside date = IF ( MAX ( 'Base Table'[Date] ) >= MIN ( 'dim date'[Date] ) && MAX ( 'Base Table'[Date] ) <= MAX ( 'dim date'[Date] ), 1, 0 ) Pro code1 = CALCULATE ( LASTNONBLANK ( 'Base Table'[Product Code], 1 ), FILTER ( 'Base Table', 'Base Table'[Date] >= MIN ( 'dim date'[Date] ) && 'Base Table'[Date] <= MAX ( 'dim date'[Date] ) ) ) Pro code2 = CALCULATE ( LASTNONBLANK ( 'Base Table'[Product Code], 1 ), FILTER ( 'Base Table', MAX ( 'Base Table'[Date] ) <= MIN ( 'dim date'[Date] ) || MAX ( 'Base Table'[Date] ) >= MAX ( 'dim date'[Date] ) || MAX ( 'Base Table'[Date] ) = BLANK () ) ) flag = IF([Pro code1]=[Pro code2],1,0)
Add 'dim date'[Date] into slicer. Insert two table visuals, in the first one, add field [Product Code] and measure [Total]. In the second one, add field [Product Code]. Also, click the second table visual, add measures [outside date] and [flag] into visual level filters, and set corresponding filter value to 0.
Result.
I have uploaded pbix file for your reference.
Best regards,
Yuliana Gu
Hi @ketan10,
Suppose your base table is called 'Base Table'. Create a calendar table.
dim date = CALENDAR ( MIN ( 'Base Table'[Date] ), MAX ( 'Base Table'[Date] ) )
In 'Base Table', create measures like below:
Total = CALCULATE ( SUM ( 'Base Table'[Total Qty Sold] ), FILTER ( 'Base Table', 'Base Table'[Date] >= MIN ( 'dim date'[Date] ) && 'Base Table'[Date] <= MAX ( 'dim date'[Date] ) ) )
outside date = IF ( MAX ( 'Base Table'[Date] ) >= MIN ( 'dim date'[Date] ) && MAX ( 'Base Table'[Date] ) <= MAX ( 'dim date'[Date] ), 1, 0 ) Pro code1 = CALCULATE ( LASTNONBLANK ( 'Base Table'[Product Code], 1 ), FILTER ( 'Base Table', 'Base Table'[Date] >= MIN ( 'dim date'[Date] ) && 'Base Table'[Date] <= MAX ( 'dim date'[Date] ) ) ) Pro code2 = CALCULATE ( LASTNONBLANK ( 'Base Table'[Product Code], 1 ), FILTER ( 'Base Table', MAX ( 'Base Table'[Date] ) <= MIN ( 'dim date'[Date] ) || MAX ( 'Base Table'[Date] ) >= MAX ( 'dim date'[Date] ) || MAX ( 'Base Table'[Date] ) = BLANK () ) ) flag = IF([Pro code1]=[Pro code2],1,0)
Add 'dim date'[Date] into slicer. Insert two table visuals, in the first one, add field [Product Code] and measure [Total]. In the second one, add field [Product Code]. Also, click the second table visual, add measures [outside date] and [flag] into visual level filters, and set corresponding filter value to 0.
Result.
I have uploaded pbix file for your reference.
Best regards,
Yuliana Gu
Thanks a lot @v-yulgu-msft ! Sorry for the delay in getting back. Your solution works really well.
I optimised it a bit more, as per the requirement. Thanks a lot once again 🙂
Cheers,
Ketan !
How can is there product unsold has within "Total Qty Sold" field?
Hi,
If your dataset don´t have Date_field for unsold product then it´s impossible filter from Date. Is there no granularity between Sold Products (where there is Date from Sales) and Unsold Products. How can you filter unsold products without Date from Sales?
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |