Challenging DAX Problem

[pawlowski6132]

Ok, I've been struggling with this all day.

I have two tables: 'Forecast' and 'Working Days'.

The 'Forecast' table has month and  forecast amount [FK Forecast].

The 'Working Days' table has [Date] and Working or Non Working indicator [Working Day]

I'm trying to write DAX (I want a calculated column) that will:

1. Iterate throuth the Forecast table and, for each row, evaluate the month and find the corresponding [FK Forecast]

2. For the month in that row of the Forecast table, calculate the working days based on the 'Working Days' table.

3. For each month, calculate the average forecast amount per working day

4. Populate that amount in the [avg_daily_fcst] column

Thanx in advance.

joe

[Syndicate_Admin]

I was able to replicate this and can confirm it works as described. I'll have some challenges because my dataset is a bit more complicated and I will have to modify to fit but, thanx SO much. I was missing the correct modeling and use of the RELATED function. You're the best. Thank you for taking the time to help me.

[pawlowski6132]

Sure, you can see the sample tables in my original post. Here is some of my work so far.

[Payeras_BI · Accepted solution]

Hello @pawlowski6132 ,

I've invented my forecast numbers, but I hope it helps

[PaulDBrown]

@pawlowski6132

Any chance you can publish a sample of your tables to work with?

[pawlowski6132]

For example.

February 2021 Forecast = 17,270

Working Days in February 2021 = 17

Average Volume per Working Day in February = 1,016

Put 1,016 for each "Working" day row where Month is Febraury.