Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
In a report I have visualized "Planned delivery dates" and "Actual delivery dates" in a table with the order number (uniqe).
I have a mock version of the table here:
Order number | Planned date (YYMMDD) | Actual date (YYMMDD) | Difference in days |
A1 | 2022-01-01 | 2022-02-01 | 30 |
B2 | 2022-02-01 | 2022-01-15 | -15 |
C3 | 2022-03-01 | 2022-04-15 | 45 |
D4 | 2022-04-01 | 2022-01-01 | -90 |
E5 | 2022-05-01 | 2021-05-01 | -365 |
I have added a slicer with relative date (ex. last 6 months) and have played around with Planned dates and Actual dates.
The problem is I always miss some data as the dates can differ quite significantly in time (+100 days in some cases).
In my head the solution is quite simple, a slicer with Planned date OR Actual date occurring in the last X months. Anyone know if this is possible?
Solved! Go to Solution.
Hi @LinaP_ ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create a date dimension table and apply it to the slicer
Note: Please DO NOT create any relationship with the fact table
2. Create a measure as below
Flag =
VAR _selpdate =
SELECTEDVALUE ( 'Table'[Planned date (YYMMDD)] )
VAR _seladate =
SELECTEDVALUE ( 'Table'[Actual date (YYMMDD)] )
RETURN
IF (
(
_seladate >= MIN ( 'Calendar'[Date] )
&& _seladate <= MAX ( 'Calendar'[Date] )
)
|| (
_seladate >= MIN ( 'Calendar'[Date] )
&& _seladate <= MAX ( 'Calendar'[Date] )
),
1,
0
)
3. Apply a visual-level filter on the visual with the condition(Flag is 1)
Best Regards
Thanks for the help! It captures all orders now that has an end date, which is more than I could solve! 😄
One issue still remains, which I did not include in my example, is that some of the "Actual" dates haven't happened yet:
Order number | Planned (YYMMDD) | Actual (YYMMDD) | Difference in days |
A1 | 2023-01-01 | 2023-01-01 | 0 |
B1 | 2023-01-01 | 2023-02-01 | 30 |
C1 | 2023-05-01 | 2023-04-15 | -15 |
D1 | 2023-05-01 | 25 |
Do you have any idea on how to include that?
Warm Regards
Hi @LinaP_ ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Create a date dimension table and apply it to the slicer
Note: Please DO NOT create any relationship with the fact table
2. Create a measure as below
Flag =
VAR _selpdate =
SELECTEDVALUE ( 'Table'[Planned date (YYMMDD)] )
VAR _seladate =
SELECTEDVALUE ( 'Table'[Actual date (YYMMDD)] )
RETURN
IF (
(
_seladate >= MIN ( 'Calendar'[Date] )
&& _seladate <= MAX ( 'Calendar'[Date] )
)
|| (
_seladate >= MIN ( 'Calendar'[Date] )
&& _seladate <= MAX ( 'Calendar'[Date] )
),
1,
0
)
3. Apply a visual-level filter on the visual with the condition(Flag is 1)
Best Regards
Hi,
Really appreciate the help!
Needed to do a modification to work with blank dates as following: but now it works like a charm.
Flag =
VAR _selpdate =
SELECTEDVALUE ( 'Table'[Planned date] )
VAR _seladate =
IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Actual date] ) ), TODAY(), SELECTEDVALUE ( ''Table'[Actual date] ) )
RETURN
IF (
(
_selpdate <= TODAY() &&
(
(
_selpdate >= MIN ( 'Calendar_for_measurment'[Date] )
&& _selpdate <= MAX ( 'Calendar_for_measurment'[Date] )
)
||
(
_seladate >= MIN ( 'Calendar_for_measurment'[Date] )
&& _seladate <= MAX ( 'Calendar_for_measurment'[Date] )
)
)
),
1,
0
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |