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 there,
I am trying to create records for 35+ days, 60+ days, 90+ days
Thanks for the response, The provided solution is not working.
You cannot create a relationship between the calendar table and the period table since it appears you used the calendar table to construct the period table.
You can create a calculated column in your calendar table using a switch statement to create your periods.
It could look something like...
Period =
SWITCH(
TRUE(),
DATEDIFF(Calendar[Date], UTCNOW(), DAY) > 90, "90+ Days",
DATEDIFF(Calendar[Date], UTCNOW(), DAY) >= 60, "60-90 Days",
DATEDIFF(Calendar[Date], UTCNOW(), DAY) >= 35, "35-59 Days",
"Current"
)
Proud to be a Super User! | |
I understand, Thanks for the response.
However this is not our business requirement.
Our Client Business requirement is to show records which are
35+ days ( Include all records which missed target > 35 days)
60+ days ( Include all records which missed target > 60 days)
90+ days ( Include all records which missed target > 90 days)
It means 35+ has more records than 60+ days & 60+ days has more records than 90+ days.
Example:
2024-03-24 : This record will fall under categorys like 35+
2024-02-24 : This record will fall under all categorys like 35+ & 60+
2023-11-24 : This record will fall under all categorys like 35+, 60+, 90+
--------------------------------------------------------------
The similar logic works if days are inbetween, PBI doesnt throw error
Period =
VAR
_Last30days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-30,UTCTODAY())
)
,"In the last","30 days"
)
VAR
_Last60days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-60,UTCTODAY())
)
,"In the last","60 days"
)
VAR
_Last90days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-90,UTCTODAY())
)
,"In the last","90 days"
)
RETURN UNION(_Last30days,_Last60days,_Last90days)
Reference Video: How to create 30/60/90/Lifetime filter in Power BI (youtube.com)
Ok, I understand your requirement.
If you need to create a second date table with the periods you cannot refer to the first calender table.
Something like this might work for you.
Table 2 =
var _vCalendar = CALENDAR(MIN(tbl[Target Date]), MAX(tbl[Target Date]))
var _35 =
ADDCOLUMNS(
FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >35),
"Period", "35+ Days"
)
var _60 =
ADDCOLUMNS(
FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >60),
"Period", "60+ Days"
)
var _90 =
ADDCOLUMNS(
FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >90),
"Period", "90+ Days"
)
RETURN
UNION(_35,_60,_90)
Just mimic the code in your calendar table in the _vCalendar variable. This should allow for the relationship to be built between the two date tables.
Proud to be a Super User! | |
Thanks for the response,
Here we forgot one more thing, we will be having duplicate records in Calendar table as well. Now I cant have relation for Calendar and my Fact table.
Even if relation happens Cardinality will be many to many.
My response was not clear. I intend for you to have both a calendar table and a period table proposed in my last response. The period table would filter the calendar table which would then filter your fact table.
Proud to be a Super User! | |
Thanks for the response, The provided solution is not working.
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 |
---|---|
86 | |
84 | |
66 | |
62 | |
60 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |