Measure transformation from varying on all dates to lastdate of the month

[Anonymous]

Dear all,

I hope you are well.

I have three different tables:

- A calendar table;

- A visitors table with the logins, one row represents one login.

I calculate the Weekly Active Users (WAU). The formula I use requires me to compute it for each date, like that:

I would need to have the WAU as measure for the very last date of the period and not depending on/for each date, so, one datapoint for each month.

Here is the original formula:

WAU =

var currentDate = SELECTEDVALUE('calendar'[Date])

var date7DaysAgo = FIRSTDATE(DATEADD('calendar'[Date], -7, DAY))

RETURN

CALCULATE(

DISTINCTCOUNT('Visitors'[Visitor ID])

,'calendar'[Date] <=currentDate,

'calendar'[Date] > date7DaysAgo)

Thank you in advance for your help! I appreciate it.

I am at your disposal should you have any questions.

A

[amitchandak · Accepted solution]

@Anonymous , for that you need a measure like

MTD Sales = CALCULATE(lastnonblankvalues('Date'[Date],DISTINCTCOUNT('Visitors'[Visitor ID])) ,DATESMTD('Date'[Date]))

[Anonymous]

I would need to keep the base formula the same as it is a metric.

[Anonymous]

I worked around it, it works! I did not know the function LASTNONBLANKVALUE.

Thank you amitchandak! You are an angel!