Total up to particular date

[Anonymous]

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.

[Kfausch]

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]

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]

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

[Anonymous]

@az38 , thanks.

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

I can only select a measure at this part.

Thanks again

[az38 · Accepted solution]

@Anonymous 

sorry, my bad 😞

<SELECTEDVALUE(Member_members[Date])

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

[Anonymous]

@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]

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

Thank you

[az38]

@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

[Anonymous]

Hi @az38 , 

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

[az38]

@Anonymous 

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

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