Calendar
=========
DateDimension =
ADDCOLUMNS (
CALENDAR (DATE(YEAR(min(Orders[Order Date])),1,1), DATE(YEAR(MAX(Orders[Order
Date])),12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))
===============================================================
Custom Fiscal Year (Australia : july 7 to june 6)
"F Month" , MONTH(EDATE([Date],-6)),
"F Quarter", "Q"&ROUNDUP(MONTH(EDATE([Date],-6))/3,0),
"F Year" , IF(MONTH([Date])>6,YEAR([Date])+1,YEAR([Date])),
===============================================================
Current Month (System Month) Sales up to and including Today =
CALCULATE
([Total Sales],
YEAR('Order Date and Sales'[Order Date]) =YEAR(TODAY()),
MONTH('Order Date and Sales'[Order Date]) =MONTH(TODAY()),
DAY('Order Date and Sales'[Order Date]) <=DAY(TODAY())
)
Current Month (System Month) Sales up to and including Today REVISED =
CALCULATE
([Total Sales],
DATESBETWEEN(
'Order Date and Sales'[Order Date],
date(YEAR(TODAY()),MONTH(TODAY()),1),
TODAY()
))
Current Month (System Month) Sales up to and including Today REVISED 2 =
CALCULATE
([Total Sales],
DATESBETWEEN(
'Order Date and Sales'[Order Date],
EOMONTH(TODAY(),-1)+1,
TODAY()
))
Current Month Full (System Month Sales) =
CALCULATE
([Total Sales],
MONTH('Order Date and Sales'[Order Date]) =MONTH(TODAY()),
YEAR('Order Date and Sales'[Order Date]) =YEAR(TODAY())
)
Last 1 Month Sales = CALCULATE(
[Total Sales],
DATESINPERIOD(
'Order Date and Sales'[Order Date],
max('Order Date and Sales'[Order Date]),
-1,
MONTH)
Last N Month Sales = CALCULATE(
[Total Sales],
DATESINPERIOD(
'Order Date and Sales'[Order Date],
max('Order Date and Sales'[Order Date]),
-SELECTEDVALUE('How many Months'[Value]),
MONTH))
==============
PreviousYearOrders (DATEADD) = CALCULATE(
SUM(Orders[Orders]),
DATEADD('DateDimension'[Date],-1,YEAR)
)
PreviousYearOrders (PARALLELPERIOD) = CALCULATE(
SUM(Orders[Orders]),
PARALLELPERIOD('DateDimension'[Date],-1,YEAR)
)
PreviousYearOrders (PREVIOUSYEAR) = CALCULATE(
SUM(Orders[Orders]),
PREVIOUSYEAR('DateDimension'[Date])
)
PreviousYearOrders (SAMEPERIODLASTYEAR) = CALCULATE(
SUM(Orders[Orders]),
SAMEPERIODLASTYEAR('DateDimension'[Date])
)
---------------
TOPN(<N_Value>, <Table>, <OrderBy_Expression>, [<Order>[, <OrderBy_Expression>,
[<Order>]]…])
Return value
A table with the top N rows of Table or an empty table if N_Value is 0 (zero) or
less. Rows are not sorted in any particular order.
TOPN does not guarantee any sort order for the results.
TOPN(
10,
SUMMARIZE(
InternetSales,
InternetSales[ProductKey],
"TotalSales", SUM(InternetSales[SalesAmount])
),
[TotalSales], DESC
)
===========
The following measure formula returns the top 10 products_sold by sales amount.
= SUMX(
TOPN(
10,
SUMMARIZE(
InternetSales,
InternetSales[ProductKey],
"TotalSales", SUM(InternetSales[SalesAmount])
),
[TotalSales], DESC
),
[TotalSales]
)
============
DATESYTD & TOTALYTD
====================
DATESYTD: Returns a set of dates in the year up to the last date visible in the
filter context.
Year to date:=CALCULATE(
SUM([Quantity]),
DATESYTD(Calendar[Date])
TOTALYTD: Evaluates the specified expression over the interval which begins on the
first day of the year and ends with the last date in the specified date column
after applying specified filters.
YTD:=TOTALYTD(
SUM([Quantity]),
Calendar[Date]
=======
USERELATIONSHIP
CALCULATE (
[Sales Amount],
USERELATIONSHIP ( Sales[Delivery Date], 'Date'[Date] )
)
====================
To show percentages in each column of the stacked bar taking the bar as 100%;
using items on the legend; in this example "Category" is the legend.
Stacked Column
Regional(x) Sum(Sales)(y) by Category (legend)
% calc 2 =
DIVIDE(sum(Orders[Sales]),
CALCULATE(
sum(Orders[Sales]),
ALLSELECTED(Orders[Category])
)
