Arithmetic from diff periods of time off of the same table

[mfa]

Dear Community,

during any given time I'm trying to create a measure that will help me count the actives of N Days Ago

for example, during 3rd of Jan if I want to count of actives 2 days ago it would be 8.

and 4-Jan (current)(8) minus 1-Jan (2 days)(6) ago would be =  8 (Active Occurances) - 6 (Active Occurances) = 5

Date	Active
1-Jan
1-Jan
1-Jan
1-Jan
1-Jan
1-Jan	Active
1-Jan	Active
1-Jan	Active
1-Jan	Active
1-Jan	Active
1-Jan	Active
1-Jan	Active
1-Jan	Active
2-Jan	Active
2-Jan	Active
2-Jan	Active
2-Jan	Active
2-Jan	Active
2-Jan	Active
2-Jan	Active
2-Jan	Active
2-Jan	Active
3-Jan	Active
3-Jan	Active
3-Jan	Active
3-Jan
3-Jan
3-Jan
3-Jan
3-Jan
3-Jan
3-Jan
3-Jan
3-Jan
3-Jan
4-Jan
4-Jan
4-Jan	Active
4-Jan	Active
4-Jan	Active
4-Jan	Active
4-Jan	Active
4-Jan	Active
5-Jan	Active
5-Jan	Active
5-Jan	Active
5-Jan	Active
5-Jan	Active
5-Jan	Active
5-Jan	Active
5-Jan
5-Jan
5-Jan
5-Jan
5-Jan
5-Jan
5-Jan
5-Jan
5-Jan

I'm using days as an example but what I really want is to go back months behind.

So Far I've tried a simple Date Count.

Count_Measure = CALCULATE(COUNT(Table[Date]),LASTDATE(date(year(Table[Date]),MONTH(Table[Date]),DAY(Table[Date])-2)))

 it didn't work

thanks in advance

[Greg_Deckler]

@mfa Seems like if you have a date range slicer you could do something like this:

Measure =
  VAR __MaxDate = MAX('Date'[Date])
  VAR __MinDate = MIN('Date'[Date])
RETURN
  COUNTROWS(FILTER('Table',[Date]<=__MaxDate && [Date] >=__MinDate && [Active]="Active"))

[mfa]

thank you for your quick reply,

im not after a range, im trying to compare between two individual dates

[Greg_Deckler]

@mfa Whether a date range slicer or not, the same logic applies. Get a date somehow. Use simple math like [Date] - 30 or a whole host other other ways to caclulate your other date. Might help if I understood what you actually wanted as output because the example you provided doesn't make any sense 8 - 3 = 5, I have no idea where you are getting those numbers from the sample data provided.

[mfa]

Correction: it would be 8-6

number of actives in 4th of jan minus number of actives in 1st of jan

I'm imagining something like this 

Calculate(Count(Date[Date]), Date[Date] = Date[Date] - 3)

But obviously, this didn't work

[v-rzhou-msft · Accepted solution]

Hi @mfa 

I think you can build two slicer to select the date you need in your measure.

Build two calculated tables to create slicer.

Current Date = VALUES('Table'[Date])

Previous Date = VALUES('Table'[Date])

Measure:

Difference =
VAR _Previous =
    SELECTEDVALUE ( 'Previous Date'[Date] )
VAR _Current =
    SELECTEDVALUE ( 'Current Date'[Date] )
VAR _CountPreviousActive =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Date] = _Previous,
        'Table'[Active] = "Active"
    )
VAR _CountCurrentActive =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Date] = _Current,
        'Table'[Active] = "Active"
    )
RETURN
    ABS ( _CountCurrentActive - _CountPreviousActive )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.