Forum Discussion
Double Loop for measure
Anonymous , Have separate date and time table , joined to date part and time part of your date
Date part = datevalue([Datetime])
Time Part = Timevalue([Datetime])
Join them with respective dim
then have measures like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])),
filter(Time, Time[Time] > time(9,0,0) && Time[Time] < time(22,0,0) ) )
or
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]))
, filter(Time, Time[Time] > time(9,0,0) && Time[Time] < time(22,0,0) ) )
You can have only hour table if needed
Time Table
https://kohera.be/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
https://radacad.com/script-for-creating-time-table-in-power-bi-with-hours-minutes-and-seconds-buckets
Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
Hi Amitchandak, thank you so much for your input.
I'm looking at the idea and i could try to join my date and time table but still I'm missing the cumulative part that exceeds the 43 per hour.
If I understood right your measure proposals:
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]))
, filter(Time, Time[Time] > time(9,0,0) && Time[Time] < time(22,0,0) ) )
This will sum all the operations (in this case sales) between dates and specified times, but I only need to sum per hour the exceeding 43 operations:
If 10am has 60 operations I need to sum = 60- 43 = 17
If 11 am has 40 operations I don't sum anything but keep the 17 and so on.
So far I can calculate exceeding operations in selected hour, now i just need to start making the sum of those values.
Thanks again for dedicating me some of your time.
Regards.