Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I 'm trying to create a measure that will give me the distinct count of instances where the date is last month.
Could you please help?
In the example below it should result 3
Thank you
Solved! Go to Solution.
Create a measure with this:
Count = CALCULATE ( DISTINCTCOUNT ( 'Table'[Key] ); MONTH ( 'Table'[Start date] ) = MONTH ( TODAY () ) - 1 )
Hope it helps!
Thank you @Anonymous for getting back to me.
Unfortunately that solution doesn't work for me, because I have duplicated values in both columns, that the computer can't handle.
"Too many arguments were passed to the DISTINCTCOUNT function. The maximum argument count for the function is 1."
Any idea on how to dedupe those? Start date is a calculated column.
Thanks again
@Anonymous Could you share a your PBIX with the example? It should work
Yes sure @Anonymous , how should I do it?
Share it with onedrive or dropbox or something.
Regards
hi @Anonymous
here it is:
https://1drv.ms/u/s!AqJUpeVVowTZgb5Ih7BAuN-4q-3IYw?e=74XVVJ
I have removed any irrelevant columns etc
thank you very much
Hi @Anonymous
Thanks again for responding.
Applying this formula on a dataset that includes data from 2 years, it doesn't distiguish this year from previous one.
I tried adding the year in the formula but didn't work:
Try using :
DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)
Count =
CALCULATE( DISTINCTCOUNT(Logs[Key]),
DATESINPERIOD(Logs[Start date],
LASTDATE(Logs[Start date],-1,MONTH)
)
brilliant, thank you very much @Anonymous .
I'm so grateful!
Hello-
I only see 2 dates in your list that are August. But the best way to do this would be to create a new column in PowerQuery. Add a column and extract "After delimiter." Select /. You might need to do it twice since there are 2 /'s. Then you can do a DAX measure for distinct count of that new measure.
Jared
hi @Anonymous
Thank you for your reply. Last month in this case is September since today is October.
I'm not sure why you mention August. Please have in mind this just a sample and there are thousands of rows.
I am hoping to have a measure that I will not need to update every year
Ah you're right, I forgot! But in any case, try this solution. It should work for you.
Jared
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |