How to Extract Data from Date Column Differences in Power BI

[jesusssss]

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. 

[Daniel29195 · Accepted solution]

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

[some_bih]

Hi @jesusssss it should be new CALCULATED column, this is standard Power BI featues so you have option.

[some_bih]

Hi @jesusssss there is no 14.12.

Simple if column is blank could solve your problem in new column?

[jesusssss]

Hi,  

I have tried but I am also not capable of adding a new column, do you know any solution?

Thanks.