cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Arithmetic from diff periods of time off of the same table

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

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous

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.

5 REPLIES 5
Super User

@Anonymous 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"))``````

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Community Support

Hi @Anonymous

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.