Monthly target that changes according to the date slicer

[carvalho]

Hello

I need help building a measure for targets.

I have an excel file with a list of sales people and their monthly targets. So it is easy to compare the sales in relation to target on a monthly basis. But, how can I build a measure that takes into account the selected period as well? So, when I change date slicer from 1 month to two months for example, the target measure will be also calculated as 2* target (etc)?

Thanks in advance.

[carvalho]

Thanks a lot! That helped.

[Mikelytics]

Great and thank you for your feedback!

[Mikelytics · Accepted solution]

Hi @carvalho ,

It depends a little bit on your case. In most Cases DISTINCTCOUNT() is what would serve your purpose. Lets say you work with a date table and you only show values from one year, then you could do something like:

DISTINCTCOUNT(DateTable[MonthColumn])

When you want to do calculation over multiple year and you have a separate data table then it is recommended to create another column which combines year and month (e.g. 2022-01, 2022,02 ...). Then you can use

DISTINCTCOUNT(DateTable[CombinedPeriod])

When you have a list of dates in a column and each value is the first or last date of a month (so only one value per period) then you could also do 

DISTINCTCOUNT(Table[PeriodColumn])

Look at my example for the second version, which I recommend to use (separate date table with year-month column):

Extract of my date table:

Now I added the measure in a KPI card and a slicer for the selection

Since you also have a plan value you can now add the the measure the plan value and you will get you result based on the selection of periods.

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.