WTD Calculation

[Anu]

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

Week to Date Calculation in Power BI with DAX

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)

[AntrikshSharma]

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

Hide dates after specific dates in Running Total or any other scenario

If you ever want to create a visual with Running total and hide dates after a certain date then you can first create a calculated column in your date table like this: Date Less Than My Date ? = Dates[Date] < DATE ( 2009, 7, 5 ) [1] After that create a visual with your date hierarchies, I generally use Matrix, and then you can create a piece of DAX like this one [3] And then add a new filter through the filter pane by using the column from the dates table created in the previous step, like…

[Anu]

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:

[mno]

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.

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

[EnterpriseDNA]

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!

[EnterpriseDNA]

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!