% Change in users : today vs yesterday

[neetu14]

I am trying to calculate % change in the number of users every day (today value - yesterday value)

It is a direct query report so I cannot use the PreviousDay function. I tried using this formula but it gives 0 values.

DIVIDE(
CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]), Table_Name[Column_Name] = TODAY()),
CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]), Table_Name[Column_Name] = TODAY()-1)

Can someone please help 🙂

[v-deddai1-msft · Accepted solution]

Hi.

Hi @neetu14 ,

Would you please try this:

DIVIDE(

CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]),ALL(Table_Name), Table_Name[Column_Name] = TODAY()),

CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]),ALL(Table_Name),  Table_Name[Column_Name] = TODAY()-1)

Best Regards,

Dedmon Dai

[amitchandak · Accepted solution]

@neetu14 , Make sure you create a date table and do it using that. All such case you might require and it will end removing other filters

DIVIDE(
CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]), filter(Date,Date[Date] = TODAY())),
CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]), filter(Date,Date[Date] = TODAY()-1))
)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Other formula's

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all(Table),Table[Date] =MAXX(FILTER(all(Table),Table[Date]<max(Table[Date])),Table[Date])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Day))

[parry2k]

@neetu14 try this

DIVIDE(
CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]), Table_Name[Column_Name] = TODAY()),
CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]),ALL(), Table_Name[Column_Name] = TODAY()-1)
)

the best practice is to add date dimension and work from there.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[neetu14]

I tried the formula you provided but no luck 😞

0 result

[v-deddai1-msft · Accepted solution]

Hi.

Hi @neetu14 ,

Would you please try this:

DIVIDE(

CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]),ALL(Table_Name), Table_Name[Column_Name] = TODAY()),

CALCULATE(DISTINCTCOUNT(Table_Name[Column_Name]),ALL(Table_Name),  Table_Name[Column_Name] = TODAY()-1)

Best Regards,

Dedmon Dai

[parry2k]

@neetu14 As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

From date dimension, previous day calculation will be super easy.