Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating cost per day

Hi all,   I have two seperate tables with relationship as follows:   Employee ID Year Month Cost 1 2019 1 € 1000   Employee ID Department Date  1 Alfa Corp 01/01/2019 ...
Need Help
  • ZunzunUOC's avatar
    ZunzunUOC
    7 years ago

    I would create a new table with the next code:

     

    Result = SELECTCOLUMNS(CROSSJOIN(Days;Employes);"ID";Employes[Employee ID];"YEAR";YEAR(Days[Date ]);"MONTH";MONTH(Days[Date ]);"DEPARTMENT";Days[Department];"COST";(Employes[Cost]/DAY(EOMONTH(Days[Date ];DAY(Days[Date ])))*COUNTROWS(FILTER(Days;Days[Department]=EARLIER(Days[Department])))))

    Best Regards,
    Miguel

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

ZunzunUOC's avatar
ZunzunUOC
Icon for Resolver III rankResolver III
7 years ago

I would create a new table with the next code:

 

Result = SELECTCOLUMNS(CROSSJOIN(Days;Employes);"ID";Employes[Employee ID];"YEAR";YEAR(Days[Date ]);"MONTH";MONTH(Days[Date ]);"DEPARTMENT";Days[Department];"COST";(Employes[Cost]/DAY(EOMONTH(Days[Date ];DAY(Days[Date ])))*COUNTROWS(FILTER(Days;Days[Department]=EARLIER(Days[Department])))))

Best Regards,
Miguel

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