cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar
Resident Rockstar

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

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.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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

Do you have a Calendar table?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Anonymous
Not applicable

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

Yes I do have a calendar table. 

Highlighted
Resident Rockstar
Resident Rockstar

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

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.

 

Capture.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Anonymous
Not applicable

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

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors