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

Average of Distinct Dates by ID

hi folks, 

 

I have this table, may I know how can use DAX to obtain the results in the 2 black columns? 

a) Sum Gap by Distinct Date by Member ID, 

b) Count Distinct LastDate by Member ID

c) Sum Gap by Distinct Date by Member ID,  (for the most recent 3 dates), desired output = (1 + 5 + 3 ) = 9
d) Count Distinct LastDate by Member ID, (for the most recent 3 dates), desired output = (17 Jul, 12 Jul, 10 Jul) = 3Capture.PNG

 

Data attached for your convenience, sorry I don't know how to put the header properly. 

4748DingDong18 July 201817 July 201816
4748DingDong18 July 201817 July 201816
4748DingDong18 July 201817 July 201816
4748DingDong17 July 201812 July 201856
4748DingDong17 July 201812 July 201856
4748DingDong17 July 201812 July 201856
4748DingDong17 July 201812 July 201856
4748DingDong12 July 201810 July 201826
4748DingDong10 July 201809 July 201816
4748DingDong10 July 201809 July 201816
4748DingDong10 July 201809 July 201816
4748DingDong09 July 201808 July 201816
4748DingDong09 July 201808 July 201816
4748DingDong08 July 2018  6
1572VK19 July 201815 July 201844
1572VK19 July 201815 July 201844
1572VK15 July 201810 July 201854
1572VK10 July 201807 July 201834
1572VK07 July 2018  4
1572VK07 July 2018  4
1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try this column

 

Sum Gap by Distinct Date =
SUMX (
    SUMMARIZE (
        FILTER ( Table1, Table1[Member ID] = EARLIER ( Table1[Member ID] ) ),
        Table1[Date],
        Table1[Gap]
    ),
    [Gap]
)

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

b) 

Count Distinct LastDate =
CALCULATE (
    DISTINCTCOUNT ( Sheet2[LastDate] ),
    FILTER ( ALLEXCEPT ( Sheet2, Sheet2[Member ID] ), Sheet2[LastDate] <> BLANK () )
)

 

c) Which rows are the most recent 3 dates? I cannot understand the desired output.

d) It looks like the most recent 3 dates of LastDate are 17 July 2018, 15 July 2018 and 12 July 2018. But why do you say "17 Jul, 12 Jul, 10 Jul"? Besides, since you want to Count Distinct LastDate for the most recent 3 dates, the result is always 3, right?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous

 

Try this column

 

Sum Gap by Distinct Date =
SUMX (
    SUMMARIZE (
        FILTER ( Table1, Table1[Member ID] = EARLIER ( Table1[Member ID] ) ),
        Table1[Date],
        Table1[Gap]
    ),
    [Gap]
)

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

wow you're awesome! that solved part a)

 

I managed to get part b)

 

CALCULATE (
    DISTINCTCOUNT ( Rating[LastDate] ),
    FILTER ( ALL ( Rating ), Rating[Member ID] = EARLIER( Rating[Member ID] ) )
)

 

Would greatly appreciate if you can also solve c) 

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.