Calculate week values having daily ones

[DimaMD]

Hello Community

We have following data:

ID	Date	Value
1	01.12.2021	500000

For our task, the table means that value of 500000 is for december (because date is 01.12). The next step we do is calculate value for each day of december. For that reason we count how much days are in december (31) and then divide 500000 with 31. The result is 16129 for each day.

The next step must be that we will show table which consist of ID, number of week (in our case in december) and value, that must be count as 16129 * "amount of days in week". The result must be as following

ID	49	50	51	52	53	ALL
1	80645	112903	112903	112903	80645	50000

So what it shows?

Week 49 and week 53 has 5 days each in december, so we multiply 16129*5=80645

Week 50-52 have 7 days each, so result is 16129*7=112903

Summary of december is 500000 as it was in default value written in the beginning.

Question is - how can i calculate this in DAX 🙂

Thank you in advance!

[DimaMD · Accepted solution]

After making some calculations, I got the desired result

Auxiliary measure was "Number of selected days":

Number of selected days = DATEDIFF(MIN('Dates'[Date]),MAX(Dates[Date]),DAY)+1

[VahidDM]

Hi @DimaMD 

You can add a calendar (date) table to your model and then use that to find the solution.

https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

you need to use something like this to find the days in the week:

calculate(countrows(date[date]),filter(all(date),date[week No.]=WEEK No.)) for instance 49

share sample of your data in a text format.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[DimaMD]

hi, @VahidDM 
Example data