Calculated column to count number of weeks from a previous response in data

[adamzee]

So I have a list of stores that are called upon sales reps. The sales reps do a check of a demonstration unit each time they visit. Is there a way in PowerPivot if the demonstration unit is not operational to count the number of weeks it has not been operational? Example is below.
Any help would be appreciated.

Store Name	Demonstration Unit Operational?	Week	Number of weeks non operational *Calculated Column*
Store 1	Yes	1
Store 2	Yes	1
Store 3	Yes	1
Store 4	No	1
Store 1	Yes	2
Store 2	Yes	2
Store 3	Yes	2
Store 4	No	2	1
Store 1	Yes	3
Store 2	Yes	3
Store 3	Yes	3
Store 4	No	3	2

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[ImkeF]

Hi,
this editor strikes, will try next post:

 

[ImkeF]

Hi,
try this: =if([Demonstration Unit Operational?]="No", CALCULATE(DistinctCOUNT([Week]), Filter(Table;[Demonstration Unit Operational?]="No" &&[Store Name]=[Store Name] && [Week] "is less than" EARLIER([Week]))),blank())

"is less than" should be the symbol on your keybord

 

[adamzee]

Hi,
this editor strikes, will try next post:

Thanks heaps ImkeF, I am using the below dax formula for the spreadsheet. However am getting an error. I am fairly new to dax so any help would be appreciated.


=if([Demonstration Unit Operational?]="No", CALCULATE(DistinctCOUNT([Week])-1, Filter(Table1[Demonstration Unit Operational?]="No" &&[Store Name]=[Store Name] && [Week]<=EARLIER([Week]) )),blank())

 

[ImkeF]

What does the error message say?

The Expression around the Filter-Statement doesn't look OK: Filter(Table, condition) - you need to place a comma after the table name and then continue with the conditions:

=if([Demonstration Unit Operational?]="No", CALCULATE(DistinctCOUNT([Week])-1, Filter(Table1, [Demonstration Unit Operational?]="...

 

[adamzee]

What does the error message say?

The Expression around the Filter-Statement doesn't look OK: Filter(Table, condition) - you need to place a comma after the table name and then continue with the conditions:

=if([Demonstration Unit Operational?]="No", CALCULATE(DistinctCOUNT([Week])-1, Filter(Table1, [Demonstration Unit Operational?]="...

Thanks heaps ImkeF, that worked perfectly.

 

[billszysz]

Hi adamzee
You can also use PQ to do this and then add the result to your data model.
Code for PQ:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Indeks", 0, 1),
    FilterRows = Table.SelectRows(AddIndex, each ([Demonstration Unit] = "No")),
    GroupedRows = Table.Group(FilterRows, {"Store Name", "Demonstration Unit"}, {{"Group", each _, type table}}),
    AddIndexToTable = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Group], "NonOperWeeks", 0, 1)),
    RemOtherCol = Table.SelectColumns(AddIndexToTable,{"Custom"}),
    ExpCustom = Table.ExpandTableColumn(RemOtherCol, "Custom", {"Indeks", "NonOperWeeks"}, {"Indeks1", "NonOperWeeks"}),
    LeftOuter = Table.Join(AddIndex,{"Indeks"},ExpCustom,{"Indeks1"},JoinKind.LeftOuter),
    RemoveColumns = Table.RemoveColumns(LeftOuter,{"Indeks", "Indeks1"})
in
    RemoveColumns

You have to change "source" to your data source, of course.

Regards