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.
Hello there,
I am stuck trying to incorporate one of the requirements asked by my boss. We have a Max_Time criteria table; which is essentially - If the order is sold at the exact time of the hour (eg: 11:00 or 5:00), then Max_Hour = that exact time (11:00 or 5:00). But if the exact time is say 11:15 or 5:59, then Max_Hour = Hour -1 (10:00 or 4:00).
As you can see from the table above.
Now here's where it get's tricky -
We have the table which compares 2019 actuals (querying the database) vs. BGT (Excel sheet) vs. LY (Querying the database) and for the daily updates, should follow the max_hour rule. So if the time is 2:35pm right now and the latest order we had was at 1:30 pm, the max_hour is 12 and the values should be until the max_hour (12) only. The data is pulling all the data until 2:35pm.
I added a calculated column Max_hour_criteria = IF(Ord_hour <=Max_Hour, "Y", "N") which gives me the right logic for TODAY(). But, if I want to see the montly comparisons, it removes the late night values. eg - if the last order had a max_time of 11:30pm or even 11:59pm, the Max_Hour logic would take the values only until 10:00pm. So almost 2 hours data is not shown.
Cannot individually change the Measures - 2019, BGT and 2018 because there's a circular dependency since all the IF statements have IF(Ord_Date = TODAY()).
Kindly help me with this issue so I can resolve my data. I am thankful for your time.
Thanks!
@MFelix @Greg_Deckler @Zubair_Muhammad @parry2k
Solved! Go to Solution.
Hey guys,
I solved it. Thank you for your time!
If anyone is curious - I was using calculated columns before so I was getting circular dependencies.
I used the measures -
Max Hour Logic = IF(Max hour <= Max time, "Y", "N")
Hey guys,
I solved it. Thank you for your time!
If anyone is curious - I was using calculated columns before so I was getting circular dependencies.
I used the measures -
Max Hour Logic = IF(Max hour <= Max time, "Y", "N")
Hi @Anonymous
Sorry, i'm not clear about your Max_Time criteria.
The Max_Time criteria table seems inconsistent with your Max_Time criteria.
Best Regards
Maggie
Hi Maggie ,
Let me try to explain the criteria with examples -
I have four orders placed today (05/31/2019) at 2:01 AM, 3:00 AM, 4:30 PM, 9:59 PM. These are the max_hour for the orders.
I created another column just selecting the start of the hour from the max_hour above - start_hour
The criteria is - If max_hour = start_hour then max_hour
If max_hour <> start_hour then Hour - 1.
So, for the above examples, the max_hour criteria would make - 1:00AM, 3:00AM, 3:00PM, 8:00PM.
My table showing the values for actuals vs. bgt vs. LY should show values only until 1:00am, 3am, 3pm and 8pm respectively. Not the values until 2:01am, 4:30pm, 9:59pm.
Thanks,
J
So, I have figured out how to get the values with this logic for 2019 and BGt.
calculated columns -
2019 = IF( Ord_date = TODAY(), CALCULATE(SUM(Table, demand),Max_Hour_critera = "Y"),CALCULATE(SUM(Demand)))
BGT = IF( Maxx(Ord_Date < TODAY()),CALCULATE(SUMX(Budget)),CALCULATE(SUMX(BUDGET,Max_hour_criteria = "Y"))
Can't seem to figure out for 2018. I would be much obliged if you could help me for the same.
For 2018, I tried -
2018 = IF(Ord_date <> Today(), Calculate(SUM(LY $)), CALCULATE(SUM(LY $),MAx_Hour_Criteria = "Y"))
But I am getting circular dependency between 2018 and 2019 when I try to do that.
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 |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |