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.
Hello!
I am trying to generate a COLUMN with the data in red, based on the table below.
What I am trying to get is the Acumulative Count of the consecutives "Yes" processes per Product until you get a "No", reading from the latest date to the oldest.
For example, in the case for Product "BBB" I begin from the latest date, which is 06/2022, and check ifthis date has a "Yes". In this case it has, so I count 1. Then, i check the next latest date, which is 05/2022. Since it also has a "Yes", I count 1 more. The same with 04/2022, so I count 1 more. Then, since the next latest date is 03/2022 has a "No", I stop counting. The cumulative count is 3, so that is the value I should reach in the column in red.
Product | Process Date | Processed? | CUMULATIVE CONSECUTIVE YES LAST PROCESSED MONTHS |
AAA | 01-2022 | Yes | 0 |
AAA | 02-2022 | Yes | 0 |
AAA | 03-2022 | No | 0 |
AAA | 04-2022 | Yes | 0 |
AAA | 05-2022 | No | 0 |
AAA | 06-2022 | No | 0 |
BBB | 01-2022 | Yes | 3 |
BBB | 02-2022 | No | 3 |
BBB | 03-2022 | No | 3 |
BBB | 04-2022 | Yes | 3 |
BBB | 05-2022 | Yes | 3 |
BBB | 06-2022 | Yes | 3 |
CCC | 01-2022 | No | 1 |
CCC | 02-2022 | No | 1 |
CCC | 03-2022 | No | 1 |
CCC | 04-2022 | Yes | 1 |
CCC | 05-2022 | No | 1 |
CCC | 06-2022 | Yes | 1 |
Thanks!
Nicolás.
Solved! Go to Solution.
Hi @Nick1810
please try
Acumulative Count =
VAR CurrentProductTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Product] ) )
VAR NoTable =
FILTER ( CurrentProductTable, 'Table'[Processed?] = "No" )
VAR LastNoRecord =
TOPN ( 1, NoTable, 'Table'[Process Date] )
VAR LastNoDate =
MAXX ( LastNoRecord, 'Table'[Process Date] )
VAR YesTable =
FILTER ( CurrentProductTable, 'Table'[Processed?] = "Yes" )
VAR YesAfterNoTable =
FILTER ( YesTable, 'Table'[Process Date] > LastNoDate )
RETURN
COUNTROWS ( YesAfterNoTable )
Hi @Nick1810 ,
Please try this DAX Calculated Column to return the cumulative total you are looking for:
Cumulated Count Processed by Product =
VAR _product = 'Sample Data'[Product]
VAR _productMaxYesProcessDate =
CALCULATE(
MAX('Sample Data'[Process Date]),
FILTER(
ALL('Sample Data'),
'Sample Data'[Product] = _product && 'Sample Data'[Processed?] = "Yes"
)
)
VAR _productMaxNoProcessDate =
CALCULATE(
MAX('Sample Data'[Process Date]),
FILTER(
ALL('Sample Data'),
'Sample Data'[Product] = _product && 'Sample Data'[Processed?] = "No"
)
)
VAR _count =
COUNTROWS(
CALCULATETABLE(
'Sample Data',
FILTER(
ALL('Sample Data'),
'Sample Data'[Product] = _product &&
'Sample Data'[Process Date] > _productMaxNoProcessDate &&
'Sample Data'[Process Date] <= _productMaxYesProcessDate
)
)
)
RETURN
COALESCE(_count, 0)
Hi @Nick1810
please try
Acumulative Count =
VAR CurrentProductTable =
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Product] ) )
VAR NoTable =
FILTER ( CurrentProductTable, 'Table'[Processed?] = "No" )
VAR LastNoRecord =
TOPN ( 1, NoTable, 'Table'[Process Date] )
VAR LastNoDate =
MAXX ( LastNoRecord, 'Table'[Process Date] )
VAR YesTable =
FILTER ( CurrentProductTable, 'Table'[Processed?] = "Yes" )
VAR YesAfterNoTable =
FILTER ( YesTable, 'Table'[Process Date] > LastNoDate )
RETURN
COUNTROWS ( YesAfterNoTable )
Genious!! Thankss
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |