itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Power BI: convert date into number

I have a date column so to convert it into numbers, there are 2 options:

  • Option 1: CONVERT([argument],INTEGER)
    power bi
  • Option 2: [argument] then convert it into “whole number”
    power bi

I have a week and year columns so to convert the week into numbers:


var calc1=((DATE([argument1],1,1)-WEEKDAY(DATE([argument1],1,1),1))+1)-7 //start on Sunday WEEKDAY(XX,1), to start on Monday WEEKDAY(XX,2)
var calc2=calc1+(7*[argument2])
return
CONVERT(calc2,INTEGER)
power bi

NOTE: for WEEKDAY(XX,number), see below for more information

power bi

I have a month column so to convert it into numbers:

YEAR([argument])*12+ROUNDUP(MONTH([argument]),0)

power bi

I have a month and quarter columns so to convert the quarter into numbers:

YEAR([argument])*4+ROUNDUP(MONTH([argument])/3,0)

power bi

Interesting Topics