Help writing a dax measure

[jwint]

Hello, 

I have a table (see below)

This snapshot is part of a table that shows deliveries from the past 18 months.  I am trying to understand how to write measures that will allow me to look at this data in more detail.  For instance, how many tons per day (or week, or month) were delivered from a certain customer?  Or how many tons (per day, per week, per month) were delivered to the "TS building"?  Can anyone explain the functions I would need to do this?  Thanks much!

[Whitewater100 · Accepted solution]

Hi:

For starters you can add up those columns.

E.G.

Total Tons = SUM(YOURTABLENAME[Delivered Tons])

Total Qty = "                                     "[Qty])

NO.of Transactions = COUNTROWS(YOURTABLENAME)  I think your table may be named table.

Unique Customers = DISTINCTCOUNT(Table[Customer])

The big thing is to add a Date Table and connect it to your Date filed in Table. Also Mark Date Table as Date Table.

Table Tools> Mark as Date Table (choose Date filed)

Connect Date Table to Table based on Date.

I hope this gets you started..

Here is Date Table code. Go to New Table and Paste this in: See table name = Dates. Use Date filed from Date Table and not date filed from table when doing charts,etc..

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today()

VAR FiscalMonthEnd = 6

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

    "Day Name",FORMAT([Date],"DDDD"),

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays