Forum Discussion

jesusssss's avatar
jesusssss
Icon for Helper II rankHelper II
2 years ago
Solved

How to Extract Data from Date Column Differences in Power BI

We are experiencing a problem in creating one of our indicators in Power BI.   We have a dataset with two date columns and a status column (FECHA_CIERRE, FECHA, and ESTADO). We filter by the closur...
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    Hello jesusssss,

    if this is the desired output , please follow the below stesp : 

     

     

     step 1 ;  create a calendar table : 

    in my sample , i have create it not the best practice way, just to work on it  : 

    datetable = CALENDAR("2019-01-01","2023-12-31")
     
    step 2 : 
    in your table, please modify the start date column format to follow :  mm/dd/yyyy
     
    step3  : after step 2, go to power query, and change data type of the column from test to date. 

     

    step 4 :  link datetable to your table on date column.

     

     

     

    step 5 : create the visual  with the required columns , ( keep in mind that the start date in the visual should read from datatable ) 

     

     

     

    step 6 : 

    write this measure : 

    Measure 18 =

    VAR current_row_date = MAX(datetable[Date]) -- 2019-12-14

    VAR closing_date =
    CALCULATE(
        MAX('Table (21)'[Close date ]) ,
        REMOVEFILTERS(datetable[Date])
    ) -- 2020-03-10
    VAR code =  VALUES('Table (21)'[Code])

    VAR start_date_day =
    DAY(
        calculate(
            max('Table (21)'[Date]),
            REMOVEFILTERS(datetable)
        )
    )

    VAR start_date =
    CALCULATE(
        MAX('Table (21)'[Date]),
        REMOVEFILTERS(datetable)
    ) -- 2019-11-04

    VAR datasource =
    FILTER(
        ALL(datetable),
        datetable[Date] <= closing_date && day(datetable[Date]) = start_date_day && datetable[Date] >= start_date
    )

    VAR c =
        SWITCH(
            TRUE(),
            start_date = current_row_date  , "closed",
            current_row_date in datasource , "ongoing"
        )
     


    return  c
     
     
     
    step 7 : 
    add this measure to the visual .
     
    DONE .
     
     
     
    hope this works out for you .
     
     
    best regards
Daniel29195's avatar
Daniel29195
Icon for Community Champion rankCommunity Champion
2 years ago

Hello jesusssss,

if this is the desired output , please follow the below stesp : 

 

 

 step 1 ;  create a calendar table : 

in my sample , i have create it not the best practice way, just to work on it  : 

datetable = CALENDAR("2019-01-01","2023-12-31")
 
step 2 : 
in your table, please modify the start date column format to follow :  mm/dd/yyyy
 
step3  : after step 2, go to power query, and change data type of the column from test to date. 

 

step 4 :  link datetable to your table on date column.

 

 

 

step 5 : create the visual  with the required columns , ( keep in mind that the start date in the visual should read from datatable ) 

 

 

 

step 6 : 

write this measure : 

Measure 18 =

VAR current_row_date = MAX(datetable[Date]) -- 2019-12-14

VAR closing_date =
CALCULATE(
    MAX('Table (21)'[Close date ]) ,
    REMOVEFILTERS(datetable[Date])
) -- 2020-03-10
VAR code =  VALUES('Table (21)'[Code])

VAR start_date_day =
DAY(
    calculate(
        max('Table (21)'[Date]),
        REMOVEFILTERS(datetable)
    )
)

VAR start_date =
CALCULATE(
    MAX('Table (21)'[Date]),
    REMOVEFILTERS(datetable)
) -- 2019-11-04

VAR datasource =
FILTER(
    ALL(datetable),
    datetable[Date] <= closing_date && day(datetable[Date]) = start_date_day && datetable[Date] >= start_date
)

VAR c =
    SWITCH(
        TRUE(),
        start_date = current_row_date  , "closed",
        current_row_date in datasource , "ongoing"
    )
 


return  c
 
 
 
step 7 : 
add this measure to the visual .
 
DONE .
 
 
 
hope this works out for you .
 
 
best regards