Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 🙂
Solved! Go to 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
@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))
@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))
@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!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
I tried the formula you provided but no luck 😞
0 result
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
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |