Forum Discussion

srdjanmish's avatar
srdjanmish
Helper I
6 years ago
Solved

Date calculation - empty fields

Hi guys...

 

I have a situation, a problem. I have a Dataset, created from dataflows, that has table (table name: DataTable1) with 2 date fields. One field is for document date, and the other is for Due date, for payment.

 

1. I created table "Calendar", with calendar function, and joined it with field "Document date".

2. I created a report with date slicer (Calendar date)

3. In table calendar, added: Max selected date = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))

 

Q: I need to calculate a column that is difference beween "Max selected date" and date in column "Due date". I tried with:

MeasureDifference = DATEDIFF(MAX('Datatable1'[DueDate]),'Calendar'[Max selected date],DAY). The Idea is to have value that shows how many days have passed from DueDate until max selected date...

 

I got results that are correct, but for some rows, result is EMPTY (no value). I know there are problems with dataflows and date formats, I experienced that, and changed cols in dataformat TEXT, and it corrected historical problems, but in this case, it is not working...

 

Anyone had similar problem, or maybe I can get needed value in different way?

 

Thanks in advanced..

Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi srdjanmish ,

     

    The reason for the blank value is that your dateslicer also filters the original table. Please refer to the measure below:

     

    MeasureDifference =
VAR MaxSelectedDate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), ALLSELECTED ( 'Calendar' ) )
RETURN
    CALCULATE (
        DATEDIFF ( MAX ( 'Datatable1'[DueDate] ), MaxSelectedDate, DAY ),
        CROSSFILTER ( 'Datatable1'[Document date], 'Calendar'[Date], NONE )
    )

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

5 Replies