topic Re: DAX MTD & Prior Year in DAX Commands and Tips

DAX MTD & Prior Year

Anonymous — Thu, 09 Apr 2020 06:52:50 GMT

Hi All,

I am trying to calcuate the count for the current year & month; 2020 Jan to April (current month) and also show the same period Jan to April but for last year 2019. I have created a date table and marked it as a date table.

Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,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" )
)

which has a 1 to many relationship to my data table 'New Business' [Variation Issue Date]

Current Year DAX :

Count YTD = CALCULATE(COUNT('New Business'[Count]), DATESYTD('New Business'[Variation Issue Date]))

returns the correct count of 8206.

Prior Year DAX:

target = 9952

Count PYTD = CALCULATE([Count YTD], SAMEPERIODLASTYEAR('Date'[Date])) returns same count as current year

Count PYTD = CALCULATE([Count YTD], SAMEPERIODLASTYEAR('New Business'[Variation Issue Date])) returns error Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion

Count PYTD = CALCULATE(TOTALMTD(COUNT('New Business'[Count]),'Date'[Date]), SAMEPERIODLASTYEAR('Date'[Date])) returns blank

Count PYTD = CALCULATE(TOTALMTD(COUNT('New Business'[Count]),'New Business'[Variation Issue Date]), SAMEPERIODLASTYEAR('Date'[Date])) returns 2813

Count PYTD = CALCULATE([Count YTD], DATEADD('Date'[Date],-1, year)) returns 8206

Count PYTD = CALCULATE(count('New Business'[Count]), DATEADD('Date'[Date],-1, year)) returns 386804

I have tried a few different ways to do it and none of them work, so any help would be appreicated.

Thanks

Re: DAX MTD & Prior Year

Greg_Deckler — Thu, 09 Apr 2020 11:13:06 GMT

Can you post sample data as text so that this can be recreated? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: DAX MTD & Prior Year

Anonymous — Thu, 09 Apr 2020 11:51:10 GMT

Did you try

CALCULATE([Count YTD], DATEADD('New Business'[Variation Issue Date],-1, year))

Re: DAX MTD & Prior Year

Anonymous — Sun, 12 Apr 2020 11:42:43 GMT

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Re: DAX MTD & Prior Year

Anonymous — Mon, 13 Apr 2020 00:15:50 GMT

The variation issue date seems to have data issues, DATEADD expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

Re: DAX MTD & Prior Year

Anonymous — Mon, 13 Apr 2020 00:35:35 GMT

Indeed, time-intel functions require that a set of dates contain all dates between the max and min date of the set.

Best
D

Re: DAX MTD & Prior Year

Anonymous — Mon, 13 Apr 2020 00:49:02 GMT

What does the time intel function base the expected min & max on, My date table has generic start and end times I haven't used a column as there are more than one table connected to it. The data is a dimension table so the dates aren't consective. Can I use a FORMAT function within the DAX to get around this problem?

Re: DAX MTD & Prior Year

Anonymous — Mon, 13 Apr 2020 00:59:57 GMT

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Re: DAX MTD & Prior Year

Anonymous — Mon, 13 Apr 2020 01:30:34 GMT

Hi Greg,

Thanks for the reply. I tried inserting a table but it doesn't allow for enough rows so here's some data. All 2020 and the first three months of 2019. Just a count of a column and the data field pretty simple.

https://drive.google.com/open?id=1bIyarSPC-hoU2Wa1OTOrP1xJKWZjyukE

hope it works

Re: DAX MTD & Prior Year

Anonymous — Thu, 16 Apr 2020 02:54:44 GMT

Hi All,

I found the problem the PYTD is returning 35976 in the top table when it should be 7686 as per bottom table. So it is summing the entire year instead of the same period in the YTD measure. This is becuase the fact table date variation issue date produces the error, Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

Count YTD = TOTALYTD('_Measures NB'[Count NB], 'New Business'[Variation Issue Date])

Count PYTD = IF(ISBLANK([Count YTD]),BLANK(), CALCULATE([Count NB],SAMEPERIODLASTYEAR('Date'[Date])))

So becuase i have to use the the date table date I need a way to calculate for last year but current month eg. year -1, current months.

Thanks

Re: DAX MTD & Prior Year

Anonymous — Thu, 16 Apr 2020 05:39:48 GMT

I have wrriten the following DAX but it returns nothing.

Count PYTD =

VAR startdate = DATE(YEAR(NOW())-1,MONTH(1), DAY(1))

VAR enddate = DATE(YEAR(NOW())-1,MONTH(NOW()), DAY(NOW()))

Return

CALCULATE(

[Count NB],

FILTER('Date','Date'[Date] >=startdate && 'Date'[Date] <= enddate)

)