Forum Discussion
Helper IChange text result in next column
Great! Glad it worked. Can you mark my answer as solved please?
I think you are usually supposed to start a seperate topic for another question but there are a few ways to go about this.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
I have one that is a little more dynamic but you can swap in static dates easily enough:
Basic Calendar = CALENDAR((TODAY()-2190),(TODAY()+730))
From there, I would advise adding a date table to your reports that use time intellegence, it is very useful. My Fiscal Year runs from April 1 to Mar 31 so you might need to change that one.
DateTable = VAR _minTableDate = MIN ('Basic Calendar'[Date]) VAR _maxTableDate = MAX ( 'Basic Calendar'[Date]) VAR _minDate = IF ( ISBLANK ( _minTableDate ), DATE ( 2018, 1, 1 ), _minTableDate ) VAR _maxDate = IF ( ISBLANK ( _maxTableDate ), DATE ( 2019, 12, 31 ), _maxTableDate ) RETURN ADDCOLUMNS ( CALENDAR ( _minDate, _maxDate ), "Year", YEAR ([Date]), "Fiscal Year", IF(3>= MONTH([Date]),YEAR([Date])-1,YEAR([Date])), "Date -1", [Date]-1, "Current Year", YEAR(TODAY()), "HalfYear", IF ( MONTH ( [Date] ) <= 6, "1st Half", "2nd Half"), "MonthNumber", MONTH ( [Date] ), "Current Month", MONTH(TODAY()), "YearMonthNumber", FORMAT ( [Date], "YYYY.MM" ), "YearMonthName", FORMAT ( [Date], "YYYY. mmmm" ), "MonthName", FORMAT ( [Date], "mmmm" ), "WeekNum", WEEKNUM ( [Date], 2 ), "CurrentWeek", WEEKNUM (today(), 2 ), "LastDayofCurrentWeek", CALCULATE(MAX('Basic Calendar'[Date]),WEEKNUM('Basic Calendar'[Date], 2 ) =WEEKNUM(TODAY(),2)), "DayNum", DAY( [Date] ), "DayOfWeekNumber", WEEKDAY ( [Date], 2 ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "Current Quarter", "Q" & FORMAT(TODAY(), "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "CurrentYearQuarter", FORMAT ( TODAY(), "YYYY" ) & "/Q" & FORMAT ( TODAY(), "Q" ) )
Perhaps a conditional column within the query editor?
this link is showing excel but I think the same rules apply.
PiyushH1Hi Svoves ,
thanks for your help.
it's working now.
also , do you know how can i add dates column in power bi directly which gives me dates from 01/01/2019 till today's date not through excel upload ?
Thank you
Great! Glad it worked. Can you mark my answer as solved please?
I think you are usually supposed to start a seperate topic for another question but there are a few ways to go about this.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
I have one that is a little more dynamic but you can swap in static dates easily enough:
Basic Calendar = CALENDAR((TODAY()-2190),(TODAY()+730))
From there, I would advise adding a date table to your reports that use time intellegence, it is very useful. My Fiscal Year runs from April 1 to Mar 31 so you might need to change that one.
DateTable = VAR _minTableDate = MIN ('Basic Calendar'[Date]) VAR _maxTableDate = MAX ( 'Basic Calendar'[Date]) VAR _minDate = IF ( ISBLANK ( _minTableDate ), DATE ( 2018, 1, 1 ), _minTableDate ) VAR _maxDate = IF ( ISBLANK ( _maxTableDate ), DATE ( 2019, 12, 31 ), _maxTableDate ) RETURN ADDCOLUMNS ( CALENDAR ( _minDate, _maxDate ), "Year", YEAR ([Date]), "Fiscal Year", IF(3>= MONTH([Date]),YEAR([Date])-1,YEAR([Date])), "Date -1", [Date]-1, "Current Year", YEAR(TODAY()), "HalfYear", IF ( MONTH ( [Date] ) <= 6, "1st Half", "2nd Half"), "MonthNumber", MONTH ( [Date] ), "Current Month", MONTH(TODAY()), "YearMonthNumber", FORMAT ( [Date], "YYYY.MM" ), "YearMonthName", FORMAT ( [Date], "YYYY. mmmm" ), "MonthName", FORMAT ( [Date], "mmmm" ), "WeekNum", WEEKNUM ( [Date], 2 ), "CurrentWeek", WEEKNUM (today(), 2 ), "LastDayofCurrentWeek", CALCULATE(MAX('Basic Calendar'[Date]),WEEKNUM('Basic Calendar'[Date], 2 ) =WEEKNUM(TODAY(),2)), "DayNum", DAY( [Date] ), "DayOfWeekNumber", WEEKDAY ( [Date], 2 ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "Current Quarter", "Q" & FORMAT(TODAY(), "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "CurrentYearQuarter", FORMAT ( TODAY(), "YYYY" ) & "/Q" & FORMAT ( TODAY(), "Q" ) )