distinctcount prev QTD and YTD at same amount of days into period as today

[douggkurt]

The company I work for is a payment network where buyers can pay suppliers through our network. The data I am working with in Power BI is the full payment date, the payment ID (unique identifier), the buyer's ID (unique identifier), and the supplier's ID (unique identifier). The date table is setup as CALENDAR = CALENDAR(Today()-730,Today()) with the date column marked as the date table.

To simplify my question, let's only focus on the distinctcount of supplier IDs. I successfully created measures using DATESYTD and DATESQTD to show the distinctcount of suppliers for YTD, QTD, previous QTD and previous YTD. However, I'd like the previous QTD and previous YTD measures to return the distinctcount of suppliers as of today's date last quarter/last year instead of the final distinctcount on the last day of the previous quarter/year. This way I can calculate the Qtr over Qtr and year over year growth % accurately as well as other ideas I have. I can show this in a visual by putting the Qtr on the X axis but I'd like to show it in a table instead.

Your help is greatly appreciated!

[amitchandak · Accepted solution]

@douggkurt , for qtr you need additional columns

Qtr Start Date = DATEADD(STARTOFYEAR('Date'[Date],"4/30"),QUOTIENT(DATEDIFF('Date'[Start Of Year], 'Date'[Date],MONTH),3)*3,MONTH) // change year end date from 4/30 to you year end like 12/30 or use quarterstartdate
Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Start date],,ASC,Dense)

measure for year

YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

for qtr

This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Day] <=max([Qtr Day])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))

[douggkurt]

Thank you!  You are the man!