DAX for Current, 90 days. and YTD

[Anonymous]

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!

[parry2k]

@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.⚡

[Anonymous]

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.

[parry2k]

@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.⚡

[Anonymous]

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!

[parry2k]

@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.⚡

[Anonymous]

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.

[V-pazhen-msft]

@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.

[Anonymous]

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.

[Anonymous]

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?