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. 

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

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.

3 Replies

  • 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.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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]))