How to calculate 'Commercial Month' - a month which begins on the 14th

[Anonymous]

Hi everyone, 

I was wondering if there is a way to create a calculated column for a 'Commercial Month', that being, I need to work out sales figures from 14th-13th of each 'Commercial Month'. 

For example, lets say the first commercial month begins 14/02/2018-13/03/2018, which is February. The commercial month of March would be 14/03/2018-13/04/2018 etc etc. Is there a way (ideally without having to write lots of DAX code as i'm still very new to PowerBI and DAX) which this can be calculated?

Thanks in advance

[Greg_Deckler]

Do you have a Calendar table?

[Anonymous]

Yes I do have a calendar table. 

[v-piga-msft · Accepted solution]

Hi @Anonymous,

You could create a calucated column in your calendar table with the formula below.

Column =
VAR t =
    IF (
        DAY ( [Date] ) >= 14,
        MONTH ( [Date] ),
        IF (
            DAY ( [Date] ) <= 13,
            IF ( MONTH ( [Date] ) <> 1, MONTH ( [Date] ) - 1, 12 )
        )
    )
RETURN
    FORMAT ( DATEVALUE ( t & "-" & "2018" ), "mmmm" )

Here is the output.

Best Regards,

Cherry

[Anonymous]

That worked! Thank you very much for your help Cherry!