Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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?
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |