Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I have a measure called "Average Score" = AVERAGE('Table'[Score]).
What would be the DAX for the Current, 90-Day, and YTD for this measure?
Thank you!
@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.
https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/
And from here you can use Datesinperiod for 90 days, DatesYTD function for YTD and DatesMTD function for the current month.
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!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
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.
What about for CURRENT date? e.g. if I open the PBI dashboard today, 6/16/2020, then the measures should be showing for data as of 6/16/2020? Thanks.
@Anonymous then you can use TODAY() function
Today Avg =
CALCULATE ( AVERGAE ( Table[Column] ), DateTable[Date] = TODAY() ) )
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!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
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.
Hi, I'm so sorry, but I realized there is no data for today. In fact, the last reported data was over a month ago. Reviews are done as needed, not everyday. So, what if I don't know what the last review date was (i.e. what the most recent date when data was available), then what should the DAX be? Thank you so much!
@Anonymous may be try something like this
Last Date =
VAR __mostRecentDate = CALCULATE ( MAX ( Table[Date] ), ALL ( Table ) )
RETURN
CALCULATE ( AVERAGE( Table[Value] ), DateTable[Date] = __mostRecentDate )
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!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
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.
Sorry to be a pain, but I also researched online, and I think I should be using LASTNONBLANK function.
But I am not sure how to use LASTNONBLANK in a DAX expression.
Can you help? Thanks.
@Anonymous
Here is Lastnoblank, but I guess you could also use LastDate or Max(Date). And see DATESYTD. Something like:
lastdate = CALCULATE(AVERAGE('Table'[Score]),LASTDATE('Table'[Date]))
last 90 days = CALCULATE(AVERAGE('Table'[Score]),FITER(ALL('Table'),'Table'[Date]>=MAX('Table'[Date])-90 && 'Table'[Date]<=MAX('Table'[Date])
YTD = CALCULATE(AVERAGE('Table'[Score]), DATESYTD('Table'[Date]))
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here are the measures again (alternative):
Date Offset = DATEDIFF(TODAY(), 'DimDate'[date], DAY) *this is a calculated column
Average Score = AVERAGE(Score)
Average Score (Last90Days) = CALCULATE([Average Score], ALL('DimDate'[date]), 'DimDate'[Date Offset]>=-90)
Average Score (YTD) = CALCULATE([Average Score], ALL('DimDate'[date]), 'DimDate'[year]=YEAR(TODAY()))
But, when I visualize these measures in a bar chart - my goal is to show these 3 measures as 3 bars in a bar chart - no matter what date I choose in the date slicer, Average Score is always = Average Score (Last90Days) - WHY? and Average Score (YTD) is always blank - WHY?
Thanks so much.
Hi Paul, thank you. The measures are syntactically correct - did not error out. But the results are blank.
I have a question: what if the review dates are sporadic, e.g. there is one on 4/1, then next one if 4/16, then 4/21, etc.
So, if PBI user selects 4/21 review date in date selector, it should show the 4/21 score, and then the average score for last 90 days counting back from 4/21, and then the YTD average score for the period 1/1-4/21/20.
Is that what these measures (that you provided) do?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |