DAX for counting the number of months in a given year

[AW1976NOVA]

Hi,

I have a table that year a year column [Year].  I'd like to add a column that returns the number of months that have accrued in a given here:

For example, here is my current table:

I'd like to add a column that would return a numerical value of 12 for 2015, 2016, 2017, 2018, 2019, and 2020...but return a numerial value of 6 for June since only 6 months have 'occurred' so far in the year 2021.  2015 - 2020 obviously had 12 months.  I'd like to have this column automatically update.  Meaning, next month when I refresh the data...I'd like to have the numerical value of 7 populate for 2021 since it will be July.

Can you assist?

[Anonymous]

hi @AW1976NOVA 

In Power Query, add custom column using following calculation

= if ([Year] < Date.Year(DateTime.LocalNow())) then 12 else
if ([Year] > Date.Year(DateTime.LocalNow())) then 0 else Date.Month(DateTime.LocalNow())

Thanks!

[selimovd · Accepted solution]

Hey @AW1976NOVA ,

you can do that with the following calculated column:

Month = 
VAR vCurrentYear = YEAR(TODAY())
VAR vCurrentMonth = MONTH(TODAY())
VAR vRowYear = myTable[Year]
RETURN
    SWITCH(
        TRUE(),
        vRowYear < vCurrentYear, 12,
        vRowYear = vCurrentYear, vCurrentMonth,
        vRowYear > vCurrentYear, 0
    )

Here I define the current year and month and then compare the year of each row to the current year.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic