Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all, I want to create a date table in following format. If we select Weekly then all the date shows with week ending on the date except the latest week with "current week" same for months quarter and year. Date column Start from 31-12-2018.
Date | Time Period | Time range |
01/04/2020 | Weekly | Current week(05/04/2020) |
W/e 27/03/2020 | ||
w/e 20/03/2020 | ||
w/e 13/03/2020 | ||
01/04/2020 | Monthly | Current month (April) |
March | ||
February | ||
January | ||
01/04/2020 | Quarterly | Current quarter (Q1 2020) |
Q4 2019 | ||
Q3 2019 | ||
01/04/2020 | yearly | Current year ( 2020) |
2019 | ||
2018 |
Hi, @Shak85
Based on your description, I created data to reproduce your scenario.
Table:
TimePeriod:
Calendar:
Calendar = CALENDARAUTO()
There is a one-to-one relationship between Calendar and Table.
You may create a meausre as below.
Result =
var _timeperiod = SELECTEDVALUE(TimePeriod[Time Period])
var _date = SELECTEDVALUE('Table'[Date])
var _year = YEAR(_date)
var _month = MONTH(_date)
var _quarter = QUARTER(_date)
return
IF(
NOT(ISBLANK(_date)),
SWITCH(
TRUE(),
_timeperiod = "Weekly",
"Current week:"& "("&
CALCULATE(
FIRSTDATE('Calendar'[Date]),
WEEKNUM('Calendar'[Date]) = WEEKNUM(_date)+1
)&")"&" w/e "&
(
CALCULATE(
LASTDATE('Calendar'[Date]),
WEEKNUM('Calendar'[Date]) = WEEKNUM(_date)-1
)-1
)&" w/e "&
(
CALCULATE(
LASTDATE('Calendar'[Date]),
WEEKNUM('Calendar'[Date]) = WEEKNUM(_date)-2
)-1
)&" w/e "&
(
CALCULATE(
LASTDATE('Calendar'[Date]),
WEEKNUM('Calendar'[Date]) = WEEKNUM(_date)-3
)-1
),
_timeperiod = "Monthly",
"Current Month:"&"("&
FORMAT(_date,"mmmm")&")"&" "&
FORMAT(DATE(_year,_month-1,DAY(_date)),"mmmm")
&" "&
FORMAT(DATE(_year,_month-2,DAY(_date)),"mmmm")
&" "&
FORMAT(DATE(_year,_month-3,DAY(_date)),"mmmm")
,
_timeperiod = "Quarterly",
"Current quarter:"&"("&
"Q"&FORMAT(_date,"q")&" "&_year&")"&" "&
IF(
_quarter = 1,
"Q4"&" "&_year-1&" "&"Q3"&" "&_year-1,
IF(
_quarter = 2,
"Q1"&" "&_year&" "&"Q4"&" "&_year-1,
"Q"&(_quarter-1)&" "&_year&" "&"Q"&(_quarter-2)&" "&_year
)
),
_timeperiod = "Yearly",
"Curret year:"&"("&_year&")"&" "&
(_year-1)&" "&
(_year-2),
BLANK()
)
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-alq-msft , Thanks for this and really appreciate your time and effort, but i want to use these in slicer for example in one slicer if i select weekly then next slicer should drill down to the next level and show
Current week(05/04/2020) |
W/e 27/03/2020 |
w/e 20/03/2020 |
w/e 13/03/2020 |
this next level in drop down same for month and year.
i want to create basically in columns such as first column Date, time period and time range
Thanks,
You have to create it using summarize and union
Thanks Amit, but is there anyway you can show me the Dax for this?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |