cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ams Helper I
Helper I

Combine the name of two months

Hi everyone. I am working on creating a combined table in Power BI. 

 

I have a month calculation. And my month does not start from the 1, but from the 16th in every mont. This means that my month contains two month names (e.g. from the 16th of september to the 15 of october). I Would like a coloumn were the displayed mothes are combined, so that the month in my example will be September-October. I have all of the information of a calander stored in a Power BI DimDate Table. So i have the month names starting from the 1st, and should therefore be able to combine these. I do not know how to make Power BI recognise that the month name should be a combined name of the two months. 

 

Can anyone help me with this?

 

Thank you in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
Nskv Resolver IV
Resolver IV

Re: Combine the name of two months

You can make a calculated column like the one below

monthAlt = 
VAR __firstMonth =
    IF(
        dimKalender[DayInMonth] < 16;
        IF( 
            dimKalender[MonthInYear] = 1;
            12;
            dimKalender[MonthInYear] - 1
        );
        dimKalender[MonthInYear]
    )
VAR __secondMonth =
    IF(
        dimKalender[DayInMonth] < 16;
        dimKalender[MonthInYear];
        IF(
            dimKalender[MonthInYear] = 12;
            1;
            dimKalender[MonthInYear] + 1
        )
    )

RETURN
FORMAT(DATE(2020; __firstMonth;1); "mmm") & "-" & FORMAT(DATE(2020;__secondMonth;1); "mmm")

if it works then please mark it as the solution

View solution in original post

3 REPLIES 3

Re: Combine the name of two months

You can use this:

FORMAT('Table 2'[Date],"mmmm")&" - "&FORMAT(ENDOFMONTH('Table 2'[Date])+1,"mmmm")
Considering that [Date] is your date field column in the date table, this will give you <this month> - <next month> format
Nskv Resolver IV
Resolver IV

Re: Combine the name of two months

You can make a calculated column like the one below

monthAlt = 
VAR __firstMonth =
    IF(
        dimKalender[DayInMonth] < 16;
        IF( 
            dimKalender[MonthInYear] = 1;
            12;
            dimKalender[MonthInYear] - 1
        );
        dimKalender[MonthInYear]
    )
VAR __secondMonth =
    IF(
        dimKalender[DayInMonth] < 16;
        dimKalender[MonthInYear];
        IF(
            dimKalender[MonthInYear] = 12;
            1;
            dimKalender[MonthInYear] + 1
        )
    )

RETURN
FORMAT(DATE(2020; __firstMonth;1); "mmm") & "-" & FORMAT(DATE(2020;__secondMonth;1); "mmm")

if it works then please mark it as the solution

View solution in original post

ams Helper I
Helper I

Re: Combine the name of two months

@Nskv 

aMAZING! thank you so much 😄 worked like a charm

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors