cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Hi. I need help showing a table of ID counts by month for the current year.  The problem I am having though is I need to show all the months of the year (JAN 2023 - DEC 2023) even though some haven't happened yet. I am trying to give a SCORECARD look and feel.  All the months of the year should show a count, a zero, or be BLANK.

So in my example:

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @The_Clam ,

Please refer to my pbix file to see if it helps you.

Create a measure.

``````Measure2 =
VAR _cur_month =
MONTH ( TODAY () )
VAR _cur_year_month =
YEAR ( TODAY () ) * 100
+ MONTH ( TODAY () )
VAR _year_month =
SELECTEDVALUE ( 'DateTable'[MonthYearSort] )
VAR _count =
CALCULATE (
COUNT ( Sheet1[ID] ),
FILTER (
ALL ( Sheet1 ),
Sheet1[year] = SELECTEDVALUE ( Sheet1[year] )
&& Sheet1[MONTH] = SELECTEDVALUE ( Sheet1[MONTH] )
)
) + 0
VAR _result =
IF ( _year_month <= _cur_year_month, _count )
RETURN
_result
``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

2 REPLIES 2
Super User

Hi,

This measure works

``Measure = if(today()<=max(DateTable[Date]),COUNTA(Sheet1[ID]),0)``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Anonymous
Not applicable

Hi @The_Clam ,

Please refer to my pbix file to see if it helps you.

Create a measure.

``````Measure2 =
VAR _cur_month =
MONTH ( TODAY () )
VAR _cur_year_month =
YEAR ( TODAY () ) * 100
+ MONTH ( TODAY () )
VAR _year_month =
SELECTEDVALUE ( 'DateTable'[MonthYearSort] )
VAR _count =
CALCULATE (
COUNT ( Sheet1[ID] ),
FILTER (
ALL ( Sheet1 ),
Sheet1[year] = SELECTEDVALUE ( Sheet1[year] )
&& Sheet1[MONTH] = SELECTEDVALUE ( Sheet1[MONTH] )
)
) + 0
VAR _result =
IF ( _year_month <= _cur_year_month, _count )
RETURN
_result
``````

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.