Dax Number of Occurrence between Date and with Previous Date

[Hijbul_Bari]

Guys, Please help me with DAX code to identify count of single occurrence of "Code" between date and previous date (Two Days).

Date	Code
2/1/2022	Subscription_ACCBalance_QFTR
2/1/2022	Package_AKASH Lite Plus_RFTR
2/1/2022	Package_AKASH LITE_RFTR
2/1/2022	Subscription_Service Act_QFTR
2/1/2022	Package_AKASH Lite Plus_RFTR
2/1/2022	Camp Offer_Referral Offer_RFTR
2/2/2022	Content_Specefic Channel_CFTR
2/2/2022	Subscription_ACCBalance_QFTR
2/2/2022	Package_AKASH STANDARD_RFTR
2/2/2022	Camp Offer_Referral Offer_QFTR
2/2/2022	Error Code_E17-0_CFTR
2/3/2022	Package_AKASH STANDARD_RFTR
2/3/2022	Package_AKASH STANDARD_QFTR
2/3/2022	Package_Package Info_QFTR
2/3/2022	Package_AKASH LITE_QFTR
2/4/2022	Subscription_ACCBalance_QFTR
2/4/2022	Content_Specefic Channel_QFTR
2/4/2022	Info Update_HelpPage_CFTR
2/4/2022	Purchase_General Process_QFTR
2/4/2022	Package_AKASH STANDARD_RFTR
2/4/2022	Recharge_bKash_QFTR
2/4/2022	Camp Offer_Feb Recharge_QFTR
2/4/2022	Subscription_Classific_QFTR

Output Will be

Date	Count=1	Total	% of 1
2/1/2022	5	6	83%
2/2/2022	7	11	64%
2/3/2022	7	9	78%
2/4/2022	10	12	83%
Total	29	38	76%

Note: When Date will be 2/2/2022 then it will check both 2/1/2022 and 2/2/2022, for  2/3/2022 then it will check both 2/2/2022 and 2/3/2022. Every time it will check number of occurrence =1 from both dates  

[v-cgao-msft · Accepted solution]

Hi @Hijbul_Bari ,

Please try these measures.

Count1 = 
VAR _totalcount = 
CALCULATE (
    COUNT ( 'Table'[Code] ),
    DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -2, DAY )
)
VAR _distinctcount =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Code] ),
    DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -2, DAY )
)
RETURN
2*_distinctcount-_totalcount

Total = 
CALCULATE (
    COUNT ( 'Table'[Code] ),
    DATESINPERIOD ( 'Table'[Date], MAX ( 'Table'[Date] ), -2, DAY )
)

% of 1 = [Count1]/[Total]

Put them and the date column into the table visual. The result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Hijbul_Bari]

@amitchandak

[amitchandak]

@Hijbul_Bari , with help from date table, Create a measure like

Rolling 2 = CALCULATE(Count(Table[Code]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,DAY))

[Hijbul_Bari]

Rolling 2 = CALCULATE(Count(Table[Code]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,DAY)), 

it give me the total column but I also need the Count column where count of Code=1.

Date	Count of Code=1
2/1/2022	5
2/2/2022	7
2/3/2022	7
2/4/2022	10

[Hijbul_Bari]

CALCULATE(DISTINCTCOUNT(data[Code]),DATESINPERIOD(data[Date],MAX(data[Date]),-2,DAY))

Can you jusst help me to get only those count which is not duplicate, DistinctCount returns both single and duplicate count. I want only single count from the above formula. Any ideas..