Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Double Loop for measure

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
Need Help
Tips and Tricks

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.