Forum Discussion
Challenging DAX Problem
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
5 Replies
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.
Any chance you can publish a sample of your tables to work with?
Sure, you can see the sample tables in my original post. Here is some of my work so far.
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.
