Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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..
Solved! Go to 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 , 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
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...
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:
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
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |