Apply DAX Formula to Separate Dates

[saumitra]

Hi All, 

I am trying to apply DAX formula to a daily report. However, when new raw data is pulled in Power BI, the DAX formula gives a combined result. But, I want a separate result for each date. 

In order to understand my problem, I am attaching a sample: 

This is the data for 2 days. After applying the DAX formula, it is giving a combined result. But, I want it as follows for different dates: 

I hope my question is understandable. Kindly please provide a solution as to how to apply the formula in such a way that the answers are not combined with the former date. 

Thank you,

Regards,

Saumitra Upadhyaya

[v-jingzhang · Accepted solution]

Hi @saumitra 

You just need to add 'Table (4)'[Date] = EARLIER('Table (4)'[Date]) into your codes. Change table's name per your need. 

Countif = COUNTAX(FILTER('Table (4)','Table (4)'[Name] = EARLIER('Table (4)'[Name]) && 'Table (4)'[Company] = EARLIER('Table (4)'[Company]) && 'Table (4)'[Date] = EARLIER('Table (4)'[Date]) && 'Table (4)'[Index] <= EARLIER('Table (4)'[Index])),'Table (4)'[Company])

HC = IF('Table (4)'[Countif] <= 1, 1, 0)

SUM1 = SUMX(FILTER('Table (4)','Table (4)'[Name] = EARLIER('Table (4)'[Name]) && 'Table (4)'[Date] = EARLIER('Table (4)'[Date])),'Table (4)'[HC])

HC1 = DIVIDE('Table (4)'[HC],'Table (4)'[SUM1],0)

Additionally, you can also try below codes to get the same result.

HC2 = DIVIDE('Table (4)'[HC],CALCULATE(SUM('Table (4)'[HC]),ALLEXCEPT('Table (4)','Table (4)'[Date],'Table (4)'[Name])),0)

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[saumitra]

Hey @v-jingzhang , thank you very much! Both your solutions work perfectly. 

[amitchandak]

@saumitra , Try new columns like

countif= countx(filter(Table, [name] =earlier([name]) && [company] =earlier([company] && [Date] =earlier([Date])), [name])

HC = if([countif] <=1,1,0)

HC1 = divide([HC],Sumx(filter(Table, [name] =earlier([name])&& [Date] =earlier([Date])), [HC]))