cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## in DAX for values in current period check occurrences in previous dates (including selected period)

I have following table:

id  date

1a  01.01.19

1a  02.01.19

2a  03.01.19

3a  04.01.19

4a  02.02.19

4a  03.02.19

2a  04.02.19

3a  05.02.19

I'm trying to calculate amount of distinct ID's in selected period which has appeared more than 1 time in the period earlier to the latest date in selected period

So selecting Jan'2019 should return - 1 ("1a" counted, "2a" and "3a" skipped, since only 1 time appeared)

Selecting Feb'2019 returns 3 ("2a", "3a" and "4a" counted since appeared >1 time in Jan/Feb, "1a" is skipped since not appeared in selected period)

1 ACCEPTED SOLUTION
Super User

Hey @Anonymous

first I created a dedicated Calendar Table (this is always a best practice), I used this DAX statement (a very simplified one) to create this table:

``````Calendar =
CALENDAR("2019-01-01" , "2019-02-28")
, "Year-Month" , FORMAT(''[Date] , "YYYY-MM")
)``````

Then I created a relationship between both tables like this:

Finally, I created this measure:

``````Measure =
SUMX(
VALUES('Table'[id])
,
var _maxdate = MAX('Calendar'[Date])
var _count =
CALCULATE(COUNT('Table'[id])
, FILTER(
ALL('Calendar'[Date]) ,
'Calendar'[Date] <= _maxdate
-- uncomment if you have more than one year
-- && YEAR('Calendar'[Date]) = YEAR(_maxdate)
)
)
return
IF(_count >  1 , 1 , BLANK())
)``````

This allows to create this table visual (be aware that it's also a good idea to use the columns from the Calendar table):

Hopefully, this is what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
3 REPLIES 3
Community Champion

Hi @Anonymous

try the next technique

create 2 columns:

1.

``DateKey = FORMAT([date];"YYYY-MM")``

2.

``````CountBeforePeriod =
CALCULATE(COUNTROWS('Table');ALL('Table');'Table'[id]=EARLIER([id]);'Table'[date]<=EOMONTH(EARLIER([date]);0))``````

then create a final measure

``Measure = calculate(DISTINCTCOUNT('Table'[id]);ALLEXCEPT('Table';'Table'[DateKey]);'Table'[CountBeforePeriod]>1)``

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
Super User

Hey @Anonymous

first I created a dedicated Calendar Table (this is always a best practice), I used this DAX statement (a very simplified one) to create this table:

``````Calendar =
CALENDAR("2019-01-01" , "2019-02-28")
, "Year-Month" , FORMAT(''[Date] , "YYYY-MM")
)``````

Then I created a relationship between both tables like this:

Finally, I created this measure:

``````Measure =
SUMX(
VALUES('Table'[id])
,
var _maxdate = MAX('Calendar'[Date])
var _count =
CALCULATE(COUNT('Table'[id])
, FILTER(
ALL('Calendar'[Date]) ,
'Calendar'[Date] <= _maxdate
-- uncomment if you have more than one year
-- && YEAR('Calendar'[Date]) = YEAR(_maxdate)
)
)
return
IF(_count >  1 , 1 , BLANK())
)``````

This allows to create this table visual (be aware that it's also a good idea to use the columns from the Calendar table):

Hopefully, this is what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

@TomMartens thanks, works perfectly.

Maybe you are also aware of how to handle the following issue (just created new post 🙂)  https://community.powerbi.com/t5/Desktop/in-DAX-count-distinct-values-from-previous-dates-missing-in...

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors