count number of selected month in specific year

[davidi4524]

hi , i have a data of 

custname

partname

quant

month

year

i added with power bi a date month and year table that related to the first table.

now i whant to select a multiple period by year 2017 - 2016

i what to get two columns that will show :

number of month selected from 2016

and number of month selected from 2017

its somthing like countif in excel - if the year of the selected month is 2016 count to measure a

and if the year of the selected month is 2016 count to measure b.

how to do this? im shure its very simple 🙂

thanks for help.

[v-shex-msft]

Hi @davidi4524,

Perhaps you can try to write a measure to get the current date and use this date find out the previous year value.

Sample:

Current/Pervious = 
var currDate=MAX(Table[Date])
return
COUNTAX(FILTER(ALL(Table),[Date]=currDate),[ColumnName])+0 &"/"&
COUNTAX(FILTER(ALL(Table),[Date]=DATE(YEAR(currDate)-1,MONTH(currDate),DAY(currDate))),[ColumnName])+0

Regards,

Xiaoxin Sheng

[DoubleJ]

Hi

I have an approach that might help you. I assumed that in the calendar table is one entry for each month (e.g. always the first day of the month).

Create 2 measures for 2016 and 2017 respectively:

SelectedMonths2016 = COUNTX(FILTER('Calendar';YEAR('Calendar'[Date])= 2016);'Calendar'[Date])

SelectedMonths2017 = COUNTX(FILTER('Calendar';YEAR('Calendar'[Date])= 2017);'Calendar'[Date])

That should do the trick:

I hope this helps!

JJ

[davidi4524]

?

[DoubleJ]

Please have a try with this formula (you might have to replace the semicolons with commas):

SelectedMonths2016 = CALCULATE(
     DISTINCTCOUNT('Calendar'[Date].[Month]);
     FILTER('Calendar';YEAR('Calendar'[Date])= 2016);'Calendar'[Date])

Does this work?

[davidi4524]

its work but its count all the month of the year, i whant to coumt only the selected months by the filter.

you can solve it?

[ibarrau]

Why don't you use a Matrix instead of table. Try years on rows and count of months on value.

[davidi4524]

this its look like this after i put you syntax: somthing wrong with the numbers.the calendare table have the folowing columns:date month year.what did i do wrong?

t