Current month as default when nothing is selected on slider

[danielwhitmote]

I want measure to be default current month.

I am using this formula but it doesnt work:

Measure =

IF(ISFILTERED(Data[Month]),

CALCULATE( DISTINCTCOUNT (Data[NCM] )),

CALCULATE( DISTINCTCOUNT (Data[NCM] )),FILTER(data, MONTH(Data[month])= MONTH(TODAY()) && YEAR(Data[Year])=YEAR(TODAY())))

Could I please ask if someone will point me to my mistake?

[v-yiruan-msft]

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

[Mikelytics]

Hi @danielwhitmote 

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.

[danielwhitmote]

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.

[Mikelytics]

Hi @danielwhitmote 

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.

[danielwhitmote]

Measure =

IF(

   ISFILTERED(Date[Month Name]),

   DISTINCTCOUNT(SNC[NCM]),

   CALCULATE(

      DISTINCTCOUNT(SNC[NCM])

      ,'Date'[Month Name]= MONTH(TODAY())

      ,'Date'[Year]=YEAR(TODAY())

   )

)

Still broken. Do I put 

      ,'Date'[Month Name]= MONTH(TODAY())

      ,'Date'[Year]=YEAR(TODAY())

or 

      ,'Date'[Month Number]= MONTH(TODAY())

      ,'Date'[Year]=YEAR(TODAY())

?

[Mikelytics]

      ,'Date'[Month Number]= MONTH(TODAY())

      ,'Date'[Year]=YEAR(TODAY())

[danielwhitmote]

Its working if I pick month number

Measure =

IF(

   ISFILTERED('Date'[Month Number]),

   DISTINCTCOUNT(SNC[NCM]),

   CALCULATE( DISTINCTCOUNT(SNC[NCM] )

      ,'Date'[Month Number]= MONTH(TODAY())

      ,'Date'[Year]=YEAR(TODAY())

   )

)

I have date hierary in my Date table, I put it in the slider and remove everything apart from month. Does it have any affect? It obviously doesnt work.

[Mikelytics]

Hi  @danielwhitmote ,

yes, because you changed both Month arguments and not only the bottom one with MONTH().

Please do it as following

Measure =

IF(

   ISFILTERED('Date'[Month Name]),

   DISTINCTCOUNT(SNC[NCM]),

   CALCULATE( DISTINCTCOUNT(SNC[NCM] )

      ,'Date'[Month Number]= MONTH(TODAY())

      ,'Date'[Year]=YEAR(TODAY())

   )

)

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.

[v-yiruan-msft · Accepted 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