Issue with DAX Calculate and Filter

[amikm]

Hi,

I am trying to create a KPI (stacked bar chart)  to show the count of Products sold between Jan 21, Feb 21, March 21 and products sold between April 21, May 21, June 21.

The scenario is
1. If I sold a Product say P1 during (Jan 21, Feb 21, March 21 ) period then I should not consider the same Product in
 (April 21, May 21, June 21) and count should not populate. 

2. If a product is not sold in  during (Jan 21, Feb 21, March 21 ) period Say P2, and the same product is sold during  (April 21, May 21, June 21) then count should populate as 1

3. If a product Say P3, we made a sale during (Jan 21, Feb 21, March 21 ) period and say sales amount is 200, and we sold the same product during (April 21, May 21, June 21) period with sales amount of 300, In that case, also, we should not populate count as 1, as we made a sale already during (Jan 21, Feb 21, March 21 ) no matter what sales amount (sales amount >0)

DAX Used for measure calculation for above:

Table : Product ( Site, Sales, Product,MonthNumber ) --Columns in Product table
MeasureR1-R3Sales = CALCULATE(SUM(Product[Sales]),FILTER(Product,Product[MonthNumber]>=1 && Product[MonthNumber]<=3))

MeasureR1-R3SalesCount = CALCULATE(DISTINCTCOUNT(Product[Site]),ALL(Product[Site]),
FILTER(Product[MeasureR1-R3Sales]>0))

Table : Product ( Site, Sales, Product,MonthNumber ) --Columns in Product table
MeasureR4-R6Sales = CALCULATE(SUM(Product[Sales]),FILTER(Product,Product[MonthNumber]>=4 && Product[MonthNumber]<=6))

MeasureR4-R6SalesCount = CALCULATE(DISTINCTCOUNT(Product[Site]),ALL(Product[Site]),
FILTER(Product[MeasureR4-R6Sales]>0))

the final output I am expecting is a stacked bar chart which will show MeasureR1-R3count as 2 and MeasureR4-R6count as 1
I want to drag both the measures in the Value bucket of the stacked bar chart visual.

Thanks

[V-lianl-msft]

Create a grouping column, and then create measures based on different conditions.

Please refer to the sample .pbix

[amikm]

Hi @V-lianl-msft ,

Your solution looks perfect, but I only have a challenge with Visualisation,

I want to create a stacked bar chart like this (right-hand side), In the left-hand side visual is fine but instead of showing into two different bars, it shows in one. (stacked bar chart)

Note: Right-hand side is just for example based on the existing PBIX file, where I took, both the R1-R3 measure and R4-R7 measure in a stacked bar chart value bucket. I want count

[V-lianl-msft · Accepted solution]

Please refer:

[amikm]

Hi @V-lianl-msft ,

I missed one requirement for the same KPI, where I need to display the Sales as well.

But when I tried to add this functionality to this report, it is not displaying the Sales as expected on hovering on Measure 3 and Measure 4

Screenshot 1:


Screenshot 2:


It should show one at a time on hovering means when I hover over Measure3 it should show count and sale for R1-R3 and when I hover over Measure 4 it should show count and sale for R3-R6.

Thanks