Forum Discussion

laut's avatar
laut
New Member
5 years ago
Solved

Prior Year's Month Percentage Calculation

Hi!

I'm still a novice with Power BI but I'm having trouble with calculating the percentage comparing current month's to prior year's month's visits.

I have a measure that calculated TOTAL VISITS (summing all Visit Counts).

 

And when I tried to create another measure to calculate YoY, it gives me this error.

Is there an easier way to calculate this or how to solve this DAX error?

Thanks!

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi laut ,

     

    Please check the measure.

    Measure = 
var current_ = CALCULATE(COUNT('Table'[visit count]),ALLEXCEPT('Table','Table'[year],'Table'[month]))
var piror = CALCULATE(COUNT('Table'[visit count]),FILTER(ALL('Table'),'Table'[year]=SELECTEDVALUE('Table'[year])-1&&'Table'[month]=SELECTEDVALUE('Table'[month])))
return
IF(ISBLANK(piror),BLANK(),(current_-piror)/piror)

     

    Best Regards,

    Jay

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    You must create a Calendar Table with a year column.  Build a relationship from the Visit Date column to the Date column of the Calendar Table (Many to One and Single).  To your visual/slicer/filter, drag Year from the Calendar Table.  Write these measures

    Measure1 = counta('Table1'[Visit Count])

    Measure1 in previous year = calculate([Measure1],previousyear(calendar[date]))

    Growth = divide(([measure1]-[Measure1 in previous year]),[Measure1 in previous year])

    Hope this helps. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi laut ,

     

    Please check the measure.

    Measure = 
var current_ = CALCULATE(COUNT('Table'[visit count]),ALLEXCEPT('Table','Table'[year],'Table'[month]))
var piror = CALCULATE(COUNT('Table'[visit count]),FILTER(ALL('Table'),'Table'[year]=SELECTEDVALUE('Table'[year])-1&&'Table'[month]=SELECTEDVALUE('Table'[month])))
return
IF(ISBLANK(piror),BLANK(),(current_-piror)/piror)

     

    Best Regards,

    Jay