undefined

[Anonymous]

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 

[Anonymous · Accepted 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")

2019 = IF((MAXX('Table','Table'[Ord_Date])=TODAY()),CALCULATE(SUMX('Table','Table'[demand]),'Table'[Max Hour logic]="Y"),CALCULATE(SUMX('Table','Table'[demand])))

2018 = IF((maxx('Table','Table'[Ord_Date])<TODAY()),calculate(sumx('Table','Table'[LY $])),CALCULATE(sumx('Table','Table'[LY $]),'Table'[Max Hour Logic]="Y"))

BGT = IF((maxX(''Table','Table'[Ord_Date])<Today()),CALCULATE(Sumx('Table',[Budget])),CALCULATE(Sumx('Table',[Budget]),'Table'[Max Hour Logic]="Y"))

[v-juanli-msft]

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

[Anonymous]

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

[Anonymous]

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.