Help with DAX Filtering

[Anonymous]

Fairly new to DAX and I'm having a hard time wrapping my head around how to use filters in the following scenario. Any help would be greatly appreciated. 

I have a table like the one below which records events which occur against a sale to track dates. 

Sale Event

Sale Event ID	Sale ID	Sale Stage Event	Event Type	Event Date
1	1	Event A - Actual	Actual	01/06/2018
2	1	Event B - Actual	Actual	01/07/2018
3	1	Event A- Forecast	Forecast	01/08/2018
4	1	Event B- Forecast	Forecast	01/09/2018
5	2	Event A- Actual	Actual	01/10/2018
6	2	Event B- Actual	Actual	01/11/2018
7	2	Event A- Forecast	Forecast	01/12/2018
8	2	Event B- Forecast	Forecast	01/01/2019
9	3	Event A- Actual	Actual	01/02/2019
10	3	Event B- Actual	Actual	01/03/2019
11	3	Event A - Forecast	Forecast	01/04/2019
12	3	Event B- Forecast	Forecast	01/05/2019

The date column is joined to a date dimension which contains a week ending date column and a year column. 

The report has drop downs which the user can select to view the sales activity for any given week in a year. 

Example parameters in the Power BI Report:
Week Ending:	27/01/2019
Year:	2019

The three measures I need to calculate are below. Points one and two I have been able to solve, but point 3 has me stumped. 

• No. of Sales where event A has actually occurred to Date:

= CALCULATE ( DISTINCTCOUNT ( SaleEvent[Sale ID]),SaleEvent[Sale Stage Event] = "Event A Actual", FILTER(ALL(DIM_DATE),DIM_DATE[WEEK_ENDING]<=MAX(DIM_DATE[WEEK_ENDING])))

Result from example = 2

• No. of Sales where event B is forecast to occur this year:

= CALCULATE ( DISTINCTCOUNT( SaleEvent[Sale ID]),SaleEvent[Sale Stage Event] = "Event B Forecast", FILTER(ALL(DIM_DATE),DIM_DATE[WEEK_ENDING]<=MAX(DIM_DATE[WEEK_ENDING]) && DIM_DATE[FINANCIAL_YEAR] = MAX(DIM_DATE[FINANCIAL_YEAR])))

Result from example = 2

• No. of Sales where event A has actually occurred to date and event B is forecast to occur this year. 

The result from example would be 1 sale. Sale ID 2. Its the only sale where event A had actually occured before the week ending date and that Event B was forecast to occur before the end of the year. 

I've not been able to acheive this within the CALCULATE / FILTER approach and searching online has been fruitless.

Any help would be greatly appreciated. 

[v-lili6-msft]

hi, @Anonymous 

I have test on my side by these three formula

Point 1 = 
CALCULATE (
    DISTINCTCOUNT ( SaleEvent[Sale ID] ),
    SaleEvent[Sale Stage Event] = "Event A- Actual",
    FILTER (
        ALL ( DIM_DATE ),
        DIM_DATE[WEEK_ENDING] <= MAX ( DIM_DATE[WEEK_ENDING] )
    )
)

Point 2 = 
CALCULATE (
    DISTINCTCOUNT ( SaleEvent[Sale ID] ),
    SaleEvent[Sale Stage Event] = "Event B- Forecast",
   FILTER (
       ALL ( DIM_DATE ),
        --DIM_DATE[WEEK_ENDING] <= MAX ( DIM_DATE[WEEK_ENDING] )&&
            DIM_DATE[FINANCIAL_YEAR] = MAX ( DIM_DATE[FINANCIAL_YEAR] )
    )
)

Point 3 = 
CALCULATE (
    DISTINCTCOUNT ( SaleEvent[Sale ID] ),
    SaleEvent[Sale Stage Event] = "Event B- Forecast",
   FILTER (
       ALL ( DIM_DATE ),
        DIM_DATE[WEEK_ENDING] <= MAX ( DIM_DATE[WEEK_ENDING] )&&
            DIM_DATE[FINANCIAL_YEAR] = MAX ( DIM_DATE[FINANCIAL_YEAR] )
    )
)

Result:

and here is my sample pbix file, please try it.

If not your case, Please share your sample pbix for us. You can upload it to OneDrive and post the link here.

Best Regards,

Lin

[Anonymous]

Thank you both for your help. 

I've created an example .pbix. 

https://cpplc365-my.sharepoint.com/:u:/g/personal/jon_murphy_cpplc_com/EZs1zmDIuDdNkoqmLGrQdu4BWgTCj...

The solutions provided are useful, but dont quite meet the requirement on point 3. 

Essentially I believe that point 3 is intersect of point 1 and 2. My DAX terminology isnt too hot so its hard to describe. This pseudo sql might help. 

SELECT COUNT(DISTINCT SALE_ID)
FROM SaleEvent
INNER JOIN DIM_DATE
ON SaleEvent.Date = DIM_DATE.date
WHERE (SaleStageEvent = 'Event A - Actual' AND DIM_DATE.WEEK_ENDING <= @Weekending)
OR
(SaleStageEvent = 'Event B - Forecast' AND DIM_DATE.Year = @Year)

Its the number of sales where both Event A has actually occured in the past and Event B is forecast to occur in the year. 

Hope that helps clarify and many thnaks fro your help. 

[v-lili6-msft]

hi, @Anonymous 

I use this formula for point 3 in your sample report, it works well.

3) No. of Sales where event A has actually occurred to date and event B is forecast to occur this year = 
CALCULATE (
    DISTINCTCOUNT ( 'Sale Event'[Sale ID] ),
    'Sale Event'[Sale Stage Event] = "Event B - Forecast",
   FILTER (
       ALL ( DimDate ),
        DIMDATE[WEEK_ENDING] <= MAX ( DimDate[WEEK_ENDING])&&
            DimDate[YEAR]= MAX ( DimDate[YEAR] )
    )
)

Why it doesn't  quite meet the requirement on point 3?

Best Regards,

Lin

[Ashish_Mathur]

Hi,

In your initial post, you mention that the answer for measure1 should be 2 - how?  I think it should be 1.  Sale ID3 registered "Event A- Actual" on or before 27 January 2019.  if you want to ignore the Year 2019 and look at all "Event A- Actual" on or before 27 January 2019 then the answer should be 3 (ID1, ID2 and ID3).  I cannot understand the answer as 2 for measure 1.

Could you kinldy review your source dataset and your expected result?  Share the revised dataset and the expected result.

[Anonymous]

Thanks for your response and apologies for the delay in this response.

I havent made any changes, but I've filtered the table to show how I think the answer to measure (1) should be 2. 

On two occasions event A actually occured before 27/01/1019. 

Hope that helps clarifiy. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  If you cannot do so, then share the Dim_Date Table with the Date, Week and Year columns.