Red Gig

Credit -  https://www.sqlbi.com/articles/using-generate-and-row-instead-of-addcolumns-in-dax/ Expressions used below: Var , Calendar , Date , Return, Generate , Year , Month , Row , Format , Calendar 6 = VAR BaseCalendar =     CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2018, 12, 31 ) ) RETURN     GENERATE (         BaseCalendar,         VAR BaseDate = [Date]         VAR YearDate = YEAR ( BaseDate )         VAR MonthNumber = MONTH ( BaseDate )         VAR YearMonthNumber = YearDate * 12 + MonthNumber – 1         RETURN ROW (             "Day", BaseDate,             "Year", YearDate,             "Month Number", MonthNumber,             "Month", FORMAT ( BaseDate, "mmmm" ),           ...

The most useful tip / trick in Microsoft office (possibly) If you're often sharing documents and using multiple locations (including sharepoint and/or OneDrive) then the following might be of use to you. Its the 'Document Location' feature in the Quick Access Toolbar. Once its added, you can always see the location of the document that is loaded and also copy the location for a hyperlink with 2 clicks (one to select it, another to copy). This function is available in all of the main office applications (Word, Excel, PowerPoint) and needs to be added manually to each application to work. This is what it looks like when its enabled To enable it do the following: 1. click the down triangle option on the Quick Access Toolbar and select 'More Commands' 2. Once the next window opens, select 'All Commands' from the 'Choose commands from' drop down 3. From the list below, navigate to 'Document Location' and select it and click the ...

Pivot tables... I'm sure its a phrase that spreads the fear of the unknown in offices all over the country, it certainly did with me before I decided to bite the bullet and familiarize myself with this alien function in Excel. Pivot tables could be filed alongside other functions and applications that make you realize analysis that previously was too labour intensive to consider or even impossible is now available within a couple of clicks. Once you've cracked the basics of a pivot table, you'll be able to make decisions on massive data sets in minutes (or less). What is a pivot table? A pivot table is a tool that enables very large datasets to be manipulated and summarised very quickly and often without having to modify the original dataset. The pivot table exists alongside a data source and 'pulls' data from the source into the pivot table. It will not modify the original dataset in any way so it lends itself to trial and error learning. Pivot tables...

  [ Measures] = {Blank()} [DAX DateDim] =  VAR BaseCalendar = CALENDAR ( date ( 2010, 1, 1) , today() ) RETURN // Create a date table with 1 line per day for the range 1/1/1990 to 31/12/2060 GENERATE ( BaseCalendar, VAR TodayDayCycle = (today() - date( 1989, 12, 17) * 1)-(14*((ROUNDUP(ABS((today() - date( 1989, 12, 17) * 1)/14),0))-1)) VAR Cycles = ([Date] - 36724) / 14 VAR PayCycle = (ROUNDDOWN(Cycles,0)+1)-(ROUNDDOWN(Cycles/26,0))*26 VAR DaysInCycle = ([Date] - date( 1989, 12, 17) * 1)-(14*((ROUNDUP(ABS(([Date] - date( 1989, 12, 17) * 1)/14),0))-1)) VAR GRCFiscalYear =ROUNDDOWN(Cycles/26,0)+2000 Return ROW ( "DayNo", day([Date]), // number of current date "Day ddd", Format([Date], "ddd"), //day name, Mon, Tue, Wed, etc  "DayInCycle", DaysInCycle*1, // number of days in paycycle "WeekNo", WEEKNUM([Date]), // Gregorian week number "WeekDay", if (or (Format([Date], "ddd") = "Sat", Format([Date], "ddd...