Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
danielwhitmote
Frequent Visitor

Current month as default when nothing is selected on slider

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?
1 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

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

View solution in original post

9 REPLIES 9
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mikelytics
Resident Rockstar
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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.

 

danielwhitmote_0-1668465543847.png

 

danielwhitmote_1-1668465596192.png

 

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

 

danielwhitmote_2-1668465670284.png

 

here is what I am trying to achieve

 

danielwhitmote_3-1668465892631.pngdanielwhitmote_4-1668465924205.png

this bit has error/ calculates incorrectly

 

danielwhitmote_5-1668466001701.png

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.

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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())
 
?

      ,'Date'[Month Number]MONTH(TODAY())
      ,'Date'[Year]=YEAR(TODAY())
------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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.

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]),
   CALCULATEDISTINCTCOUNT(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.

 
------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.