CODEWITHSUNDEEP
countpowerbicomparebetween
Dina Alathur
Dina Alathur

Reputation: 1

compare the counts between two years

I am trying to report on comparing record counts between current and past years. I tried the samelastyearperiod function and the dateadd function, but I haven't had any luck. Can you please help me? Thank you.

I created a fiscal year (starting from 1 April) calendar table (Date) that contains just dates, and I have a fact table that contains record ID and file-received dates.

I created the following two measures.

Count_recordID = count(recordID)

Previous year count = calculate(count_recordID,samelastyearperiod(Date_table[date]).

Inactivated the relationship between the date table and fact table.

I am not getting th desired output. Someone can help. Thank you.

enter image description here

I am trying to compare the counts between current and past years.

I tried creating a fiscal calendar table and created two measures.

Count_recordid = count(record_id)
compare_LY = calculate(count_recordId,sameaslastyear(date[dates]))

I am having date table which contains only date values and a fact table that contains recordid column and the record received date column. Mapped date column and received date column.

Still no luck. Someone, can you please help me, with what mistakes I am making? Thank you.

enter image description here

Upvotes: -1

Views: 39

Answers (1)

Nick A
Nick A

Reputation: 1124

I recommend the following:

  1. Create a Date Table using the year start and year end of both the earliest date value and latest date value.
  2. Create active relationship between both fact and date table.
  3. Create your measures.
  4. Insert measures into table.

1. Create a Date Table using the year start and year end of both the earliest date value and latest date value.

_Date = 
VAR minYear = YEAR(MIN('Table'[Date]))
VAR maxYear = YEAR(MAX('Table'[Date]))

VAR minDate = DATE(minYear, 1, 1)
VAR maxDate = DATE(maxYear, 12, 31)

RETURN
ADDCOLUMNS(CALENDAR(minDate, maxDate )
, "Year", YEAR([Date])
)

2. Create active relationship between both fact and date table.

data model

3. Create your measures.

ThisYear = COUNT('Table'[ID])

PrevYear = CALCULATE([ThisYear], SAMEPERIODLASTYEAR('_Date'[Date]))

4. Insert measures into table.

example

Upvotes: 0

Related Questions