Forum Discussion

saumitra's avatar
saumitra
Icon for Helper I rankHelper I
5 years ago
Solved

Apply DAX Formula to Separate Dates

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.  ...
Need Help
Show and Tell
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
saumitra
Icon for Helper I rankHelper I
5 years ago

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