Return a Value based On Date Period

[SamFletch]

Hello all,

I have table which outlines some percentages based on month:

Month	Year	Period ID	Category	Value %
01	2021	012021	Red	40%
01	2021	012021	Blue	20%
01	2021	012021	Green	40%
02	2021	022021	Red	60%
02	2021	022021	Blue	15%
02	2021	022021	Green	25%
03	2021	032021	Red	50%
03	2021	032021	Blue	25%
03	2021	032021	Green	25%
04	2021	042021	Red	25%
04	2021	042021	Blue	45%
04	2021	042021	Green	30%

I'm trying to write a measure that displays the percentage of a specific category for the current month. For example If I wanted to see Red Values, it would show 40% in January and then change autmatically to 60% in Febuary and 50% in March.

As you can see, I've broken the data down to their month, year and an ID for that entire period. In my data I've got a date dimension with the same Period ID to reference against but I'm really struggling with the measure itself as I'm nout sure how the time itenllignece works. 

What would be the best way to write a measure to get the results I need?

Thank you very much.

[BA_Pete]

Hi @SamFletch ,

If there's only ever one value for each colour in each month and you will be splitting the colours within a visual, then the following should work:

_yourValue = MAX(yourTable[Value %])

If you need to specify colours in separate measures (i.e. there's no context filter, like table rows etc.) then the following should work:

_yourColourValue =
CALCULATE(
  MAX(yourTable[Value %]),
  yourTable[Category] = "Red"
)

Pete 

[SamFletch]

Good afternoon Pete, Thank you for the quick response. Whilst the forumals you've given return the percentage value, it's returning the maximum of any of the months. Using my data posted as an example, filtering by red returns the value 60%, even though it's only 60% in February and not the other months. 

It's the time intelligence that's really getting me. 

[BA_Pete · Accepted solution]

Hi @SamFletch ,

Apologies, I've re-read your post and you only want to show CURRENT month values.

I think the quickest way to do this would be to add a [currentMonth] field into your date table.

In DAX it would be something like this:

currentMonth =
IF(
  MONTH(calendar[Date]) = MONTH(TODAY())
  && YEAR(calendar[Date]) = YEAR(TODAY()),
  "currentMonth",
  BLANK()
)

In Power Query M it would be something like this:

currentMonth =
if Date.Month([Date]) = Date.Month(DateTime.LocalNow()) and Date.Year([Date]) = Date.Year(DateTime.LocalNow()) then "currentMonth" else null

You can then use this field from your calendar table to apply visual or page-level filters to only show current month data. You would then use the measures I provided before in visuals to actually display the data.

In fact, you can roll this into any measure you want as well so you don't need to filter visuals, something like this:

_yourColourValue =
CALCULATE(
  MAX(yourTable[Value %]),
  yourTable[Category] = "Red",
  calendar[currentMonth] = "currentMonth"
)

Pete

[SamFletch]

This makes sense and I feel like it should work but I just can't get the current month formula to work. When I do it in DAX, everything comes up blank and when I do it in M I get a "Token Eof expected error and it doesn't work. 

[BA_Pete]

HI @SamFletch ,

Silly question, but have you changed the table and field names in my measures to match yours? The table and field names I used match my test tables, but might not match yours.

Also, the 'Token Eof' error usually means that you haven't copied all the code, or that you aren't implementing it correctly. For the M code, you need to go to the Add Column tab in Power Query, hit Custom Column, put your required column name in the New column name field, then paste everything AFTER "currentMonth =" into the Custom column formula field. Again, make sure that you change any reference to [Date] in my example to whatever the field name is in your calendar table that contains contiguous dates.

Pete