Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I want measure to be default current month.
I am using this formula but it doesnt work:
Solved! Go to Solution.
Hi @danielwhitmote ,
Please update the formula of measure as below and check if you can get the expected result:
Measure =
IF (
ISFILTERED ( 'Date'[Month Name] ),
DISTINCTCOUNT ( SNC[NCM] ),
CALCULATE (
DISTINCTCOUNT ( SNC[NCM] ),
FILTER (
'Date',
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
&& 'Date'[Month Number] = MONTH ( TODAY () )
)
)
)
If the above one can't help you get the desired result, please provide some sample data in your table 'SNC' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @danielwhitmote ,
Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.
Best Regards
I identified some topics which definitely should be adusted
1) if there is nothing special the calculate around the firtst distinct count is not needed
2) there was a ")" wrong i think
3) your MONTH() of data[month] does not seem right since MONTH() is for date values and I assume your month column is a number and not a date . same for the year column
Can you please try the following:
MEasure =
IF(
ISFILTERED(Data[Month]),
DISTINCTCOUNT(Data[NCM]),
CALCULATE(
DISTINCTCOUNT(Data[NCM] )
,Data[month]= MONTH(TODAY())
,Data[Year]=YEAR(TODAY())
)
)
if this does not work I would need an example of the relevant tables which you use for your measure as well as the relations between the tables if you use multiple ones.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Your function calculates valus only for current month correctly. When date/month is selected or unselected on slider. However it returns blank when any other month is selected.
I have data table which has date columns for year/month but I also have separate table - date.
And this is my separate date table that I dont use for this calculation. Relatinship is many to one SNC to date.
I use this table for slider
here is what I am trying to achieve
this bit has error/ calculates incorrectly
I wouldnt bother with this however I have to use it in my next measure in this format and thats why I am trying to solve this issue.
I assume in the slicer you put the date table minth correct? If yes the this should the explenation.
Can you please try the following:
IF(
ISFILTERED(Date),
DISTINCTCOUNT(Data[NCM]),
CALCULATE(
DISTINCTCOUNT(Data[NCM] )
,Date[month]= MONTH(TODAY())
,Date[Year]=YEAR(TODAY())
)
)
its a small change but could have big impact. I use the month and year as well as the ISFILTERED on the datetable. In the slicer you also need to put in the month names from the slicer. And the date table needs a 1:n relationship to the data table
another options (depending on how you use it in the visuals) is checking ISFILTERED only on the Month Name since you use this one in the slicer.
IF(
ISFILTERED(Date[Month Name]),
DISTINCTCOUNT(Data[NCM]),
CALCULATE(
DISTINCTCOUNT(Data[NCM] )
,Date[month]= MONTH(TODAY())
,Date[Year]=YEAR(TODAY())
)
)
@danielwhitmote I edited my response, please check again if you have already read it
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Its working if I pick month number
Hi @danielwhitmote ,
yes, because you changed both Month arguments and not only the bottom one with MONTH().
Please do it as following
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi @danielwhitmote ,
Please update the formula of measure as below and check if you can get the expected result:
Measure =
IF (
ISFILTERED ( 'Date'[Month Name] ),
DISTINCTCOUNT ( SNC[NCM] ),
CALCULATE (
DISTINCTCOUNT ( SNC[NCM] ),
FILTER (
'Date',
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
&& 'Date'[Month Number] = MONTH ( TODAY () )
)
)
)
If the above one can't help you get the desired result, please provide some sample data in your table 'SNC' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
111 | |
59 | |
57 |