DAX function Date requires 3 parameters as number
Date(year, month, day)
Example: Date(2022, 2, 17)
This will give date as "2022-02-17"
DateValue converts a text string to date time
Example: we have year in one column and month name as "feb" in another column.
And we want to combine year & month to date column, we can write as below
Date table can be created in power bi in multiple ways:
Using the CALENDAR DAX function:
In Power BI, click New table.
Name the query, example it can be dim_date or just date or date_dim
dim_date = CALENDAR(DATE(2017, 01, 01), DATE(2027-12-31))
This will create dim_table with column "date" having dates ranging from 1-jan-2017 to 31-dec-2027
Tricky part to remember: Parameters inside CALENDAR has to be date. We just can't hard-code '2017-01-01' and '20127-12-31'
Thats the reason we use DATE function
CALENDARAUTO DAX function:
CALENDARAUTO() is a function that scans all datetime columns in the model. Picks the min & max dates from those columns and returns contiguos dates as table with "Date" column.
Optionally we can pass number 1 to 12 to the function which is considered as the end month of fiscal year
Creating Calendar Date using M Langauage/PowerQuery
Open Power Query, take the blank query and type below M code:
{Number.From(#date(2017,9,1))..Number.From(#date(2022,12,31))}
Convert the list to table and change the data type to date.
TOTALYTD
TOTALQTD
TOTALMTD
DATESYTD
DATESMTD
DATESQTD
DATEADD
SAMEPERIODLASTYEAR
PARALLELPERIOD
USERELATIONSHIP
generates table
-----------------------------------------------
DATATABLE
UNION
SUMMARIZE
DISTINCT
FILTER
GENERATESERIES
CALENDAR
CALENDARAUTO
--------------------------
Aggregate Functions:
SUM
MIN
MAX
AVERAGE
COUNTROWS
DISTINCTCOUNT
-----------------------------------
Information Functions:
ISBLANK
ISFILTERED
ISCROSSFILTERED
----------------------------------------
Logical Functions
IF
AND
SWITCH
------------------------------------------
Date & Time Functions
DATEDIFF
DATEVALUE
-------------------------------------------
FILTER Functions
CALCULATE
FILTER
VALUE
ALL
TOPN
#### SSAS Size ###
Param($ServerName=”localhost”)
$loadInfo = [Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
if ($server.name -eq $null) {
When below powershell query is executed, we will get cube size on the local server
Write-Output (“Server ‘{0}’ not found” -f $ServerName)
break
}
$sum=0
foreach ($d in $server.Databases )
{
Write-Output ( “Database: {0}; Status: {1}; Size: {2}MB” -f $d.Name, $d.State, ($d.EstimatedSize/1024/1024).ToString(“#,##0") )
$sum=$sum+$d.EstimatedSize/1024/1024
}
$SizeGB=$Sum/1024
write-host ‘Sum of Database = ‘$sum ‘ MB’
Write-host ‘Total Size of Cube Databases =’ $SizeGB ‘ GB’
