Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hello @pawlowski6132 ,
I've invented my forecast numbers, but I hope it helps
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.
Sure, you can see the sample tables in my original post. Here is some of my work so far.
Hello @pawlowski6132 ,
I've invented my forecast numbers, but I hope it helps
Any chance you can publish a sample of your tables to work with?
Proud to be a Super User!
Paul on Linkedin.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |