Double Loop for measure

[Cr1s88]

Hello Everyone,

I've been reading some threads about how to emulate loop in a measure and tried to apply them to my model, but haven't been able to, I'm still struggling a bit to understand with where am I going to do the +1.

Goal

To create a cumulative measure that adds all the operations that exceed 43 per hour in selected time frame (in two dimensions) between 2 selected dates and between 9am to 10pm.

The model

It's a big data base in excel, that contains per register:

Airline name, date of operation, passengers, capacity of aircraft, a support column that I created with a unitary value representing operation, etc.

Iv'e created a couple of support tables to manage time and date:

a) Date Table:

tblFecha = CALENDAR("01-01-2022","31-12-2023")

Año = YEAR(tblFecha[Fecha])

MesNum = MONTH(tblFecha[Fecha])

Mes = SWITCH(MONTH(tblFecha[Fecha]),1,"Enero",2,"Febrero",3,"Marzo",4,"Abril",5,"Mayo",6,"Junio",7,"Julio",8,"Agosto",9,"Septiembre",10,"Octubre",11,"Noviembre",12,"Diciembre")

MesCorto = LEFT(tblFecha[Mes],3)

Dia = DAY(tblFecha[Fecha])

Periodo = tblFecha[Año] & FORMAT(tblFecha[MesNum] ,"00")

b) Time Table

Time =

VAR _series =

GENERATESERIES(1,1440,1)

VAR _time =

ADDCOLUMNS ( _series, "Dia y hora", TIME ( 0, [Value],0))

RETURN

ADDCOLUMNS(

_time,

"TimeKey", FORMAT ([Dia y hora], "hhmm"),

"Actual Time", FORMAT ([Dia y hora],"HH:MM AM/PM"),

"Hour", HOUR ([Dia y hora]),

"Hour Extended", FORMAT ([Dia y hora], "HH"),

"Minute", MINUTE ([Dia y hora]),

"AMPM", FORMAT ([Dia y hora],"AM/PM")

)

The Measure

So far I can get the exceeding number of operations per hour but still can't get it to be cummulative:

a) Working Measure (only exceeding ops in earliest selected hour and day):

Test =

VAR ActualDay = MINX(ALLSELECTED(tblFecha[Fecha]),tblFecha[Fecha])

VAR ActualTime = MINX(ALLSELECTED('Time'[Hour Extended]),'Time'[Hour Extended])

    VAR OpsHora = CALCULATE(SUM('2022-2023'[Operacion]),FILTER(tblFecha,tblFecha[Fecha]=ActualDay),FILTER('Time','Time'[Hour Extended]=ActualTime))

    VAR Excedente = IF(OpsHora>43,OpsHora-43,0)

RETURN

Excedente

b) Measure on development - I'm trying to use the generateseries to create a table that emulates the loop as I've read in some threads, and I've been working in the first step which would be the cumulative sum of the first day.

 I need to develop a nested iterative measure that sums exceeding ops per hour in one day and then iterate it through selected dates:

Test 4 (not working) =

VAR MinDay = MINX(ALLSELECTED(tblFecha[Fecha]),tblFecha[Fecha])

VAR MinTime = MINX(ALLSELECTED('Time'[Hour Extended]),'Time'[Hour Extended])

VAR MaxDay = MAXX(ALLSELECTED(tblFecha[Fecha]),tblFecha[Fecha])

VAR MaxTime = MAXX(ALLSELECTED('Time'[Hour Extended]),'Time'[Hour Extended])

    VAR OpsHora = CALCULATE(SUM('2022-2023'[Operacion]),FILTER(tblFecha,tblFecha[Fecha]=MinDay),FILTER('Time','Time'[Hour Extended]=MinTime))

    VAR Excedente = IF(OpsHora>43,OpsHora-43,0)

//Loop Table

VAR LoopTimeTable = GENERATESERIES(MinTime,MaxTime,1)

//Calculos

VAR TablaCalculo = ADDCOLUMNS(LoopTimeTable,"Excedente",Excedente + SUMX(FILTER(LoopTimeTable,[Value]<=EARLIER([Value])),[Value]))

VAR MaxV = MAXX(LoopTimeTable,[Value])

//MAXX(FILTER(LoopTimeTable,[Value]=MaxV),Excedente)

RETURN

MaxV

 Would love to have some ideas, thanks in advance

[amitchandak]

@Cr1s88 , 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-bucket...

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-bd52912a5b...

[Cr1s88]

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.