Zero % to be calculated for last week

[Krishna_Newuser]

Data will be refreshed once on Monday, and it will have the data from (26th May to 1st June). Likewise, if the data is refreshed next week (Monday) it will have the data from (2nd June to 8th June).
Whenever data refreshed, I just need to count of stores which has 0% in the adoption from the recent value. For this week we need to show the values from  (26th May to 1st June).

Note: I have the separate calender table from PBI.

DAX used: Check = CALCULATE(COUNT('Adoption Trend_Dup'[NewAdoption]), FILTER(ALL('Adoption Trend_Dup'), WEEKNUM('Adoption Trend_Dup'[TxnDt],1) = WEEKNUM(TODAY(),1)-1))

Data from PBI:

Store ID	Store Name	Adoption%	TxnDt
1	AA	0%	Sunday, May 19, 2024
2	AB	100%	Sunday, May 19, 2024
3	AC	70%	Sunday, May 19, 2024
4	AD	0%	Sunday, May 19, 2024
5	BB	9%	Sunday, May 19, 2024
6	BA	0%	Sunday, May 19, 2024
7	BC	0%	Sunday, May 19, 2024
1	AA	0%	Sunday, May 26, 2024
2	AB	100%	Sunday, May 26, 2024
3	AC	70%	Sunday, May 26, 2024
4	AD	0%	Sunday, May 26, 2024
5	BB	9%	Sunday, May 26, 2024
6	BA	0%	Sunday, May 26, 2024
7	BC	60%	Sunday, May 26, 2024

Expected Result from the recent data  (26th May to 1st June) will be 3.

[v-jianpeng-msft · Accepted solution]

Thanks solutions from @rajendraongole1 and @amitchandak 

Hi, @Krishna_Newuser 

First, create a virtual table with a variable, as shown in the following image:

So your measure DAX expression is:

Count =
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[Store ID],
        'Table'[Adoption%],
        'Table'[TxnDt],
        "weeknum", WEEKNUM ( 'Table'[TxnDt], 1 ),
        "weeknumtoday", WEEKNUM ( TODAY (), 1 ) - 1
    )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Adoption%] ),
        FILTER ( _table, 'Table'[Adoption%] = 0 && [weeknum] = [weeknumtoday] )
    )

Here are the results:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Krishna_Newuser]

Thank you so much @v-jianpeng-msft. This worked for me. Kudos to you 👍

[amitchandak]

@Krishna_Newuser , Make sure the store is a separate dimension table joined with you table

A measure like this will do

countx(Values(Store[Stores]), if(isblank([Check]) , [Store], blank()) )

Very similar logic used here

https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

[rajendraongole1]

Hi @Krishna_Newuser  - Create a new measure to count the number of stores with 0% adoption

I hope you already created MaxDate, StartOfWeek and EndofWeek measures, if not i will add it below after the main logic.

ZeroAdoptionCount =
VAR MaxDate = [MaxTxnDate]
VAR StartWeek = [StartOfWeek]
VAR EndWeek = [EndOfWeek]

RETURN
CALCULATE(
COUNTROWS('Adoption Trend_Dup'),
FILTER(
'Adoption Trend_Dup',
'Adoption Trend_Dup'[TxnDt] >= StartWeek &&
'Adoption Trend_Dup'[TxnDt] <= EndWeek &&
'Adoption Trend_Dup'[Adoption%] = "0%"
)
)

Max: MaxTxnDate = CALCULATE(MAX('Adoption Trend_Dup'[TxnDt]), ALL('Adoption Trend_Dup'))

Start of week 

StartOfWeek =
CALCULATE(
MIN('Calendar'[Date]),
FILTER(
'Calendar',
'Calendar'[Date] <= [MaxTxnDate] &&
'Calendar'[WeekStartDate] <= [MaxTxnDate]
)
)

End:

EndOfWeek =
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
'Calendar',
'Calendar'[Date] >= [StartOfWeek] &&
'Calendar'[WeekStartDate] <= [MaxTxnDate]
)
)

Hope the above measure help to find the count with 3 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Krishna_Newuser]

Hi @rajendraongole1 

Thank you for the reply, 
received error as "DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the value".

Also i dont have Start week and End week but i have created one currently.  
My week start day will be a sunday and Week end date will be a saturday, when i created that it shows wrongly.