Date calculation - empty fields

[srdjanmish]

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..

[amitchandak]

@srdjanmish , Try like

MeasureDifference =
Var _max =MAXX(ALLSELECTED('Calendar'),'Calendar'[Date]))
return
DATEDIFF(MAX('Datatable1'[DueDate]),_max,DAY)

You need to force a row context too for grand total refer

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[srdjanmish]

This is not good... I need result for each row in DataTable1, difference between Max selected date and DueDate. With this calculation, I get Max(DueDate) and Max selected date difference...

Grand total does not matter, it is not needed...

[srdjanmish]

Sorry, I tried as a column (without MAX on columnd DueDate), and as a measure (with MAX)...

As a measure, it shows good value, but still, on number of rows i get blank value, and DueDate exists...

Image:

[v-deddai1-msft · Accepted solution]

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

[srdjanmish]

Thx @v-deddai1-msft ,

Somewhere in back of my brain i was aware of that filter rule, but, did not use that part of brain, obviously...

Thx again, problem solved, and solution accepted...