Forum Discussion
jesusssss
Helper II
Helper IIHow 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...
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/yyyystep3 : 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-14VAR closing_date =CALCULATE(MAX('Table (21)'[Close date ]) ,REMOVEFILTERS(datetable[Date])) -- 2020-03-10VAR 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-04VAR 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 cstep 7 :add this measure to the visual .DONE .hope this works out for you .best regards
