Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Total up to particular date

Hi

 

I have a card where I show the a measure:  

MeasureCountMembers = DISTINCTCOUNT(Member_members[Member_number]) + 0
 
I then have a date hierachy slicer and when I click on a particular date, it will give me the distinct count of members for that date. 
I would however like to instead have the total distinct count up to and including this date, instead of just for the date I've clicked on. So if I click on January 5th, I would like all to see all results up to and including January 5th. 
Is this possible?
 
Thank you.
1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@Anonymous 

sorry, my bad 😞

<SELECTEDVALUE(Member_members[Date])

 

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
LinkedIn

View solution in original post

10 REPLIES 10
Kfausch
Helper II
Helper II

You might be able to use DATESBETWEEN to achieve this, I have done this for similar applications. Then you can use MAX and MIN date to get what you need.

 

https://docs.microsoft.com/en-us/dax/datesbetween-function-dax 

 

Here is an example of how I have used it.

 

Consumed Last 30d = -CALCULATE
                ([Consumption Qty],
                    DATESBETWEEN
                    ('Item Ledger Entry'[Posting Date],
                        MAX('Item Ledger Entry'[Posting Date]) -29 ,
                        MAX('Item Ledger Entry'[Posting Date])          
                    )
                 )

 

Thanks,

Kevin

amitchandak
Super User
Super User

Try. Make sure you are using date dimension

MeasureCountMembers = CALCULATE( DISTINCTCOUNT(Member_members[Member_number])),filter(date,date[date] <=maxx(date,date[date])))+ 0

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

 

az38
Community Champion
Community Champion

Hi @Anonymous 

try a measure

MeasureCountMembers = 
IF(ISFILTERED(Member_members[Date]),
CALCULATE(DISTINCTCOUNT(Member_members[Member_number]), Member_members[Date] < Member_members(Member_members[Date])),
DISTINCTCOUNT(Member_members[Member_number])
) + 0

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
LinkedIn
Anonymous
Not applicable

@az38 , thanks.

 

Is this bit correct here? < Member_members(Member_members[Date])),

I can only select a measure at this part.

 

Thanks again

az38
Community Champion
Community Champion

@Anonymous 

sorry, my bad 😞

<SELECTEDVALUE(Member_members[Date])

 

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
LinkedIn
Anonymous
Not applicable

@az38 I get this when I used selectedvalue:

A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Anonymous
Not applicable

@az38 , could you advise me how to resolve the True/False expression error?

 

Thank you

az38
Community Champion
Community Champion

@Anonymous 

try

MeasureCountMembers = 
IF(ISFILTERED(Member_members[Date]),
CALCULATE(DISTINCTCOUNT(Member_members[Member_number]), FILTER(ALL(Member_members),Member_members[Date] < SELECTEDVALUE(Member_members[Date]))),
DISTINCTCOUNT(Member_members[Member_number])
) + 0

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Hi @az38 , 

 

The Eintrittsdatum is the date. Still giving me errors unfortunately. thanks for your help.

 

Unbenannt.PNG

az38
Community Champion
Community Champion

@Anonymous 

you have lost closing parenthesys ")" in ALL() function

 

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
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.