Latest Enterprise DNA Initiatives

WTD Calculation

To do the WTD calculation, I understand the below formula can be used:

Week to Date Sales =
var CurrentDate=LASTDATE(DimDate[FullDateAlternateKey])
var DayNumberOfWeek=WEEKDAY(LASTDATE(DimDate[FullDateAlternateKey]),3)
return
CALCULATE(
SUM(FactInternetSales[SalesAmount]),
DATESBETWEEN(
DimDate[FullDateAlternateKey],
DATEADD(
CurrentDate,
-1*DayNumberOfWeek,
DAY),
CurrentDate))

But , in my case

LASTDATE cannot be used because [FullDateAlternateKey] is having future dates also.
How to tweak this formula to handle it?
sample 1_1_21.pbix (169.2 KB)

@Anu Create a column in your date table that will contain True for past dates and False for future dates and then use that column to filter dates table.

image
sample 1_1_21.pbix (167.3 KB)

I tried applying the filter, but the result is not expected.
Expected result is 129 , sum of these:
image

Hi Anu,

I understand that you are looking for a solution to sum Collections week_to_date (WTD).
If the following result seems OK, you can try the attached file.

image

Measures:

Collection =
SUM( 'To Collect'[Collections] )
Collection WTD =
VAR CurrentDate = SELECTEDVALUE( 'Date'[Date] ) 
VAR Result = CALCULATE(
    [Collection],
  'Date'[Date] <= CurrentDate,
    CALCULATETABLE(
        VALUES( 'Date'[Week Number] ),
          'Date'[DateWithData] = TRUE
    )
) 

RETURN
Result

File attached:sample 1_1_21_ready.pbix (169.7 KB)

Take care.

Mariusz

Hi @Anu, did the response provided by the users and experts helped you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

Hi @Anu, we’ve noticed that no response has been received from you since the 1st of January. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!