undefined

[akash0800]

Hi, have a scenario which I am not able to solve and need your assistance.

1. I have Measure which gives the total revenue. This is created in a revenue table.

2. Now I have NetworkDays per each month in a separate table.

3. Also, I have a Date Table.

I am not able to view the NetworkDays column while trying to write a measure.

 I created a new column in Date table and copied the NetworkDays.

Now, how do I write a measure using DAX and display the revenue per day in a month.

[Anonymous · Accepted solution]

Hi @akash0800 ,

Here I suggest you to add a NetWorkday column in your calendar table. It should look like as below.

For reference: NETWORKDAYS function (DAX) - DAX | Microsoft Learn

In my sample, 2022/01/21 is a holiday, so there are 20 working days in Jan.

Networkday = NETWORKDAYS('Calendar'[Date],'Calendar'[Date],1,{DATE(2022,01,21)})

 Measure:

Measure = DIVIDE([Revenue],SUM('Calendar'[Networkday]))

Best Regards,
Rico Zhou

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

[Arul]

@akash0800 ,

Could you provide sample data and expected output?

[akash0800]

Revenue: 84,822,567.54

NetworkDays: Jan- 20days, Feb- 20 days, Mar- 21 days..

Expected answer for Jan- 42,41,128.377

[Anonymous · Accepted solution]

Hi @akash0800 ,

Here I suggest you to add a NetWorkday column in your calendar table. It should look like as below.

For reference: NETWORKDAYS function (DAX) - DAX | Microsoft Learn

In my sample, 2022/01/21 is a holiday, so there are 20 working days in Jan.

Networkday = NETWORKDAYS('Calendar'[Date],'Calendar'[Date],1,{DATE(2022,01,21)})

 Measure:

Measure = DIVIDE([Revenue],SUM('Calendar'[Networkday]))

Best Regards,
Rico Zhou

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

[akash0800 · Accepted solution]

Thank you this worked.

As suggested. I added the network days to date table using Lookup function and then created a measure as suggested. It worked.

Thanks for the support.