adding the conditional check if the deliveredDate is less than one week from current date

[dexter]

I have a table(MyProdData) with columns as shown in the below table.DeliveredDate is of Date type.

 ID     Pname      DeliveredDate    level

100    Mobile     9/22/2016         A1
109    PC         9/23/2016         A1
188    Mobile     8/11/2016         A2
190    Laptop     8/11/2016         A2
200   TV          9/18/2016         A3
333   PC          9/24/2016         A1
377   Laptop      9/20/2016         A2
399   Mobile      9/18/2016         A3
400   Mobile      9/19/2016         A3
403   Laptop      9/22/2016         A1

I want to sum the A1's in the level column whose DeliveredDate is less than one week from current date.

A1 Count = 
CALCULATE(
COUNTAX(
FILTER ( 'MyProdData', 'MyProdData'[level] = "A1"),
'MyProdData'[level] 
))

The above formula is showing the count of A1 in the level column, but how to give the other conditional check as
count A1 level only if the DeliveredDate is less than one week from current date.

[v-juanli-msft]

Hi @dexter

Asumme the "current date" refer to today 2018/9/26, but you example data never meet the condition "DeliveredDate is less than one week from current date", so i use date of year 2018 to explain my solution.

create a column

Column = CALCULATE(COUNT(MyProdData[level]),FILTER(ALL(MyProdData),[level]="A1"&&DATEDIFF([DeliveredDate],TODAY(),DAY)<7))

Best Regards

Maggie

[dexter]

Thanks and appreciate all the inputs given above. Can i give the condition in just the FILTERS instead of creating a new measure which counts level A1 whose creation date is less than one week? As the condition for date calculation is always dynamic based on the current date, is it possible to give in filters?

[affan · Accepted solution]

@dexter

brilliant idea! Yes this can be done using the filters. Use relative date filter for the DeliveredDate.

Then apply a filter on the level field to include 'A1' only. When you display the field on the canvas in a visual, Select the count of the field.

Please mark this post as an accepted solution if this helped you.

Regards,

Affan

[affan]

Hi @dexter,

You can use the following measure to get the required count

A1 Count = var oneweek=CALCULATETABLE(MyProdData,MyProdData[DeliveredDate]>=TODAY()-7 && MyProdData[DeliveredDate]<=TODAY())
return

CALCULATE(
COUNTAX(
FILTER ( oneweek, 'MyProdData'[level] = "A1"),
'MyProdData'[level] 
))

For your reference you can find the pbix on the link below.

https://www.dropbox.com/s/sn6at7meocfytih/Adding%20conditional%20check.pbix?dl=0

Please mark this post as an accepted solution if this helped you.

Regards,

Affan

[Baskar]

@dexter

Try This.