Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Shak85
Helper I
Helper I

Can we create a date table with specific format?

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.

 

DateTime PeriodTime range
01/04/2020WeeklyCurrent week(05/04/2020)
  W/e 27/03/2020
  w/e 20/03/2020
  w/e 13/03/2020
01/04/2020MonthlyCurrent month (April)
  March
  February
  January
01/04/2020QuarterlyCurrent quarter (Q1 2020)
  Q4 2019
  Q3 2019
01/04/2020yearlyCurrent year ( 2020)
  2019
  2018
4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @Shak85 

 

Based on your description, I created data to reproduce your scenario.

Table:

b1.png

TimePeriod:

b2.png

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:

b3.png

b5.png

b6.png

b7.png

 

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,

amitchandak
Super User
Super User

You have to create it using summarize and union

 

Thanks Amit, but is there anyway you can show me the Dax for this?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.