DAX MTD & Prior Year

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Did you try 

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

[Anonymous]

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.

[Anonymous]

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

Best
D

[Anonymous]

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?

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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)

                            )