Total Sales calculation based on a dynamic flag that updates based on SELECTEDVALUE() date slicer.

[ManishShetty]

Hi everyone!

I have a measure that calculates total sales using SUM function [Total Net Sales]. I am currently using calculation groups for Time Intelligence which are working perfectly fine. The date slicer used here is based on a week ending date and it allows the user to go back in time[previous week ending date which is always a Sunday] and see the Total sales for that time period across all the time intelligence functions.

[Total Net Sales] :=
SUM ( 'FactSales'[Net Sales] )

YTD :=
CALCULATE (
SELECTEDMEASURE (),
FILTER (
ALL ( DimDate ),
( DimDate[DateDt] <= MAX ( DimDate[DateDt] ) )
&& ( DimDate[FiscalYear] = MAX ( DimDate[FiscalYear] ) )
)
)

I have another requirement where the Total sales must be calculated only when a flag status is True/1. The Flag should update dynamically based on the date slicer[a week ending date]. I have been using SELECTEDVALUE() to get the week end date from the slicer and pass it along the measure. The condition for the flag is that: Return true/1 when a fixed date[brought to Fact table from a dimension table using RELATED; a date column based on business req.] in the FACT table is less than or equal to the date selected in the slicer. This is the logic used:

[Flag] :=
IF ( 'FactSales'[Date abc] <= SELECTEDVALUE ( 'Date'[Week ending date] ), 1, 0 )

Only if this flag is true, the netsales must be calculated and use this netsales across all the calculation groups as same as [Total Net Sales]. I'm getting different results with different iterations of measure I try. 

Here is the current measure that I have which works only when WTD is selected in the Calculation group slicer. I have implemented the [Flag] logic within the measure itself since it didn't work well when I used it seperately.

Net Sales :=
CALCULATE (
[Total Net SalesAmt],
FILTER (
'FactSales',
'FactSales'[Date Comp] <= SELECTEDVALUE ( 'DimDate'[week ending date] )
)
)

An example of one of the Calculaton items:

WTD :=
IF (
HASONEVALUE ( DimDate[FiscalYear] ) && HASONEVALUE ( DimDate[FiscalWeek] ),
CALCULATE (
SELECTEDMEASURE (),
FILTER (
ALL ( Dimdate ),
( DimDate[FiscalYear] = VALUES ( DimDate[FiscalYear] ) )
&& ( DimDate[FiscalWeek] = VALUES ( DimDate[FiscalWeek] ) )
&& ( DimDate[DateDt] <= MAX ( DimDate[DateDt] ) )
)
)
)

I aslo tried creating an unrelated Date table for the week slicer and the calculation group and update the measure to work based off that table. It still does not do the job for me.

Any suggestions on how to handle this scenario?

Thanks in advance!
Manish

[ManishShetty · Accepted solution]

UPDATE:

Was able to get the measure to work:

Net Sales :=
CALCULATE (
[Total Net Sales],
FILTER (
'FactSales',
'FactSales'[Date abc] <= SELECTEDVALUE('Date'[Week ending date])
)
)

[ManishShetty]

Hi @Sahir_Maharaj 

Thank you for the reply!

[Flag] and 'FactSales'[Date Comp] <= SELECTEDVALUE ( 'DimDate'[week ending date] ) filters are the same. I just implemented the [Flag] logic within the measure itself since it didn't work well when I used it seperately. But they are basically the same, except that the former was created as a separate measure and the latter was used within the measure directly. I tried creating a [Flag] column in the fact table and implement it in the FILTER of the measure. But it results in a blank result. 

NetSales :=
CALCULATE (
[Total Net SalesAmt],
FILTER ( 'FactSales', 'FactSales'[Flag] = 1 )
)

'FactSales'[Flag] is a calculated column with the following as the underlying DAX. This is the dynamic flag which changes based on the date slicer. 

=IF('FactSales'[Date Comp] <= SELECTEDVALUE ('DimDate'[week ending date]), 1, 0)

This doesn't show any results in the visuals.

Thanks!

[Sahir_Maharaj]

Hello @ManishShetty,

You need to apply a dynamic filter to your [Net Sales] measure based on a flag. The [Flag] logic should be applied to filter out the rows where the flag is not true, and then apply the [Total Net Sales] calculation to the remaining rows.

 You can use a FILTER statement that checks the flag status and applies it as a condition:

Net Sales :=
CALCULATE (
[Total Net Sales],
FILTER (
'FactSales',
'FactSales'[Date abc] <= SELECTEDVALUE('Date'[Week ending date]) &&
'FactSales'[Flag] = 1
)
)

The FILTER statement applies the two conditions you need to meet for a row to be included in the calculation: the date must be less than or equal to the selected week ending date, and the flag must be 1. This measure should work for any time period selected, regardless of the calculation group.

Just make sure that the [Flag] column is present in the 'FactSales' table and is correctly updated based on the logic you described.

Let me know if you may require further guidance.