Calculating totals to date with caveat of limiting canceled records

[justinmpalmer]

date_canceled			Return
1/10/2018			1/26/2018
1/22/2018			1/26/2018
1/26/2018			1/29/2018
1/26/2018			2/26/2019
1/29/2018			3/26/2019
9/10/2019			2/22/2020
1/22/2019			3/26/2020
2/26/2019			4/26/2020
3/26/2019
10/10/2020
2/22/2020
3/26/2020
4/26/2020

I have a set of dates date_canceled and I want to get all of the records for today 1/26/2021 that have not been cancelled, but I want it to look over every year and use the month and day for each year. You can see the results I am looking for.

I am looking to count how many records that are returned on a week by week basis. Tracking applications that have not cancelled to this date and for each year.

I really don't even know where to begin.

--------------------------------------------

Update with more detail. Sorry it was late for me and I was not up to the task.

With the table "Applications" and the following information (Note: Filter Out? is not part of the table, but rather the records that need removed to have the correct result.

id	Sales Week	Sales Season Start Date	Application Received Date	Date Cancelled	Program Start Date	Filter Out?
256977	12	9/1/2017	11/22/2017	1/10/2018	8/31/2018	Yes
263896	20	9/1/2017	1/18/2018	1/22/2018	8/31/2018	Yes
262824	18	9/1/2017	1/5/2018	1/26/2018	8/31/2018	No
259859	19	9/1/2017	1/8/2018		8/31/2018	No
242897	9	9/1/2017	11/3/2017	1/29/2018	8/31/2018	No
290702	0	9/1/2018	7/19/2018	1/24/2019	8/31/2019	Yes
295249	1	9/1/2018	9/5/2018	1/5/2019	8/31/2019	Yes
262673	0	9/1/2018	1/5/2018	2/1/2019	8/31/2019	No
315053	22	9/1/2018	1/30/2019	2/13/2019	8/31/2019	No
303811	11	9/1/2018	11/16/2018		8/31/2019	No
352911	11	9/1/2019	11/19/2019	9/2/2020	8/31/2020	Yes
362368	19	9/1/2019	1/17/2020	9/9/2020	8/31/2020	Yes
357165	15	9/1/2019	12/19/2019	2/10/2020	8/31/2020	No
358855	18	9/1/2019	1/7/2020	9/11/2020	8/31/2020	Yes

Expected result:

id	Weeks to Start	Sales Season Start Date	Application Received Date	Date Cancelled	Program Start Date	Filter Out?
262824	18	9/1/2017	1/5/2018	1/26/2018	8/31/2018	No
259859	19	9/1/2017	1/8/2018		8/31/2018	No
242897	9	9/1/2017	11/3/2017	1/29/2018	8/31/2018	No
262673	0	9/1/2018	1/5/2018	2/1/2019	8/31/2019	No
315053	22	9/1/2018	1/30/2019	2/13/2019	8/31/2019	No
303811	11	9/1/2018	11/16/2018		8/31/2019	No

I calculate Sales Week by looking at the sales season and counting the number of weeks to the Program Start Date.  

Sales Week has 3 possibilities, if: 

1. Application Received Date is < Sales Season Start Date  
   1. Sales Week is 0 
2. Application Received Date is > Sales Season Start Date  
   1. Sales week is 52 
3. Otherwise  
   1. Sales week is the week that it happen 

We receive applications before and after the sales Sales Season Start Date / Program Start Date. Management does not want numbers below 0 or 52. They are okay with all sales before and after happening on 0 or 52 in those cases. 

I add the following column to Applications: 

_Sales Week = SWITCH(TRUE(), 
DATEDIFF(RELATED('Program Dates'[Sales Season Start Date].[Date]),'Applications'[application_received_on_date].[Date], WEEK) < 0, 0,
DATEDIFF(RELATED('Program Dates'[Sales Season Start Date].[Date]),'Applications'[application_received_on_date].[Date], WEEK) > 52, 52,
DATEDIFF(RELATED('Program Dates'[Sales Season Start Date].[Date]),'Applications'[application_received_on_date].[Date], WEEK) 
) 

I have the following Measure to count a running total to track sales on a week by week basis, but it does not filter out cancelled records based on the current date for each year:

Applications Running Total Week Application Received = 
CALCULATE(
    [COUNTROWS(Applications)],
    FILTER(
        ALLSELECTED(Applications[_Sales Week]),
        ISONORAFTER(Applications[_Sales Week], MAX(Applications[_Sales Week]), DESC)
    )
)

@lbendlin I have updated this with more information, please let me know if this helps or you need more detail.

[Icey · Accepted solution]

Hi @justinmpalmer ,

Please check if this could meet your requirements:

Measure =
VAR ThisMonth =
    MONTH ( TODAY () )
VAR ThisDay =
    DAY ( TODAY () )
VAR CurrentYear =
    YEAR ( MAX ( Applications[Date Cancelled] ) )
VAR Date_Today =
    IF ( CurrentYear <> BLANK (), DATE ( CurrentYear, ThisMonth, ThisDay ) )
VAR Date_0126 =
    IF ( CurrentYear <> BLANK (), DATE ( CurrentYear, 1, 26 ) )
RETURN
    IF (
        MAX ( Applications[Date Cancelled] ) = BLANK ()
            || MAX ( Applications[Date Cancelled] ) >= Date_0126,
        1
    )

For test, use "Date_0126" . For today, use "Date_Today" to get the actual result.

In addition, I have one question:

Why did the four marked records not appear in your expected results? Any other rules I ignored?

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[justinmpalmer]

Thank you @Icey,

This looks very promising. How can I incoorporate your measure with my existing running total measure?

Applications Running Total Week Application Received = 
CALCULATE(
    [COUNTROWS(Applications)],
    FILTER(
        ALLSELECTED(Applications[_Sales Week]),
        ISONORAFTER(Applications[_Sales Week], MAX(Applications[_Sales Week]), DESC)
    )
)

Your right about the 4. They should be included in the dataset. Thanks for catching this!

[Icey]

Hi @justinmpalmer ,

Try this:

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[lbendlin]

"for the given year"  is what throws me off. that sounds awfully unfair to the guys in December versus the guys in January. I think your logic is different from that.

[justinmpalmer]

If there are students that canceled before 1/26 of the given year we don't want to count them because they were cancelled. This means that we can compare year over year all students that have not cancelled up to 1/26 of each of the given years.

Any that cancelled after 1/26 should still count, because for this year we don't know who is going to cancel after 1/26 because it is not here yet.

[lbendlin]

Your Sales Week column definition seems incorrect.  Please check the rules again.

Ah, your definition is missing the "plus one year"  part for the second rule.

SW = SWITCH(TRUE(),

Applications[Application Received Date]<Applications[Sales Season Start Date],0,

Applications[Application Received Date]>EDATE(Applications[Sales Season Start Date],12),52,

DATEDIFF(Applications[Sales Season Start Date],Applications[Application Received Date],WEEK))

or more concisely:

SW = min(max(0,DATEDIFF(Applications[Sales Season Start Date],Applications[Application Received Date],WEEK)),52)

Please explain why 256977 is to be filtered out but 242897 not?

[justinmpalmer]

256977 - This is filtered out because the date cancelled is before 1/26

242897 - This is left because the date cancelled is after 1/26

Trying your Sales Week definition.

[lbendlin]

does the year matter?  What does "before 1/26"  even mean?

[justinmpalmer]

1/26 for the given year.

If they cancelled in 2017 then I can't have cancelled applications for that year that are before 1/26/2017, but I should count records that are for 1/26/2017 and after.

If they cancelled in 2018 then I can't have cancelled applications for that year that are before 1/26/2018, but I should count records that are for 1/26/2018 and after.

And so on.

[justinmpalmer]

@lbendlin any additional thoughts here? I appreciate the help if you can provide.

[smpa01]

@justinmpalmerwhat is the exact Filter Out? logic

[justinmpalmer]

This is what I don't know how to do. I have tried to write a Measure that replaced the current current month and day for each Date Canceled date that would allow me to remove the records.

There are two problems. I don't think this is a good fix, feels wrong. 2ndly, Power BI does not like it and throws syntax errors.

Something like this is what I tried:

Applications Total Not Cancelled As Of Today = 
COUNTX(
    FILTER(
        Applications,
        Applications[date_canceled].[Date] < DATEVALUE(FORMAT(TODAY(), "MM/DD/") & ALL(Applications[date_canceled].[Year])) ||
        ISBLANK(Applications[date_canceled])
    ),
    COUNTROWS(Applications)
)

[smpa01]

@justinmpalmerI am sorry. I am still confused. Whether power bi does it or not, my question is can you please describe based on what logic you want to populate the Filter out column

[justinmpalmer]

I want all records returned that are of 2 types:

1. Cancelled on or after the current month/day for the given year that the sales was made
2. Not cancelled

Does that make sense?

[smpa01]

@justinmpalmerattached.

https://drive.google.com/file/d/1ZXeWnn2z4FjXW3LfNUWCx45PZfi-1z8i/view?usp=sharing

[justinmpalmer]

Thanks for the pbx, I am not sure what this solves in relation to the rest of my post.

[lbendlin]

"I want to get all of the records" - what records? Returns? Orders?  Please provide more context and more complete sample data. Please indicate the expected outcome.

[justinmpalmer]

Updated with more context. Hopefully this will help.