topic Re: How to Extract Data from Date Column Differences in Power BI in DAX Commands and Tips

How to Extract Data from Date Column Differences in Power BI

jesusssss — Thu, 18 Jan 2024 08:01:28 GMT

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.

Re: How to Extract Data from Date Column Differences in Power BI

some_bih — Thu, 18 Jan 2024 12:40:42 GMT

Hi jesusssss there is no 14.12.

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

Re: How to Extract Data from Date Column Differences in Power BI

jesusssss — Fri, 19 Jan 2024 07:25:15 GMT

Hi,

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

Thanks.

Re: How to Extract Data from Date Column Differences in Power BI

some_bih — Fri, 19 Jan 2024 11:13:13 GMT

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

Re: How to Extract Data from Date Column Differences in Power BI

Daniel29195 — Sun, 21 Jan 2024 09:41:27 GMT

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