Forum Discussion
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 closure date, which means our status, which can be either ongoing or closed, only shows the actions that are closed, causing the ongoing ones to disappear. We want to know if it's possible to create a DAX measure that considers the month difference between FECHA and FECHA_CIERRE, assigning a 1 for each month of difference. This would allow us to sum it up and create a bar chart with both ongoing and closed actions. However, we are unable to make this 1 a value for each month; instead, it becomes a fixed value for each month.
Attached here is an Excel image of the model to help you understand the dataset and what we are trying to achieve.
Thanks.
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
4 Replies
- Daniel29195
Community Champion
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 
jesusssssHi,
I have tried but I am also not capable of adding a new column, do you know any solution?
Thanks.
