Invoicing Date Month value

[Pmorg73]

Hi everyone. I have a calendar table set up. Our invoicing is run on the 28th of the month and for a forecast I would like to have a column in my calendar taking account of this as the end of the "invoice month" being the 28th of the month.

I created a Inv Month number column by the folowing

Inv Month # = IF('00_calendar'[Day]<28, '00_calendar'[month number], '00_calendar'[month number]+1)

and this returns a numerical value. and works correctly. so for example 29/4/20 is month number 5, ie the 5th invoicing month of the year.

Is there a way to then format that back to April, May etc? I would then like to use that in the slicer and the user selects May for the WIP but it truncates to the 28th of the month. Does this make sense?

[Anonymous · Accepted solution]

Hi @Pmorg73 ,

Think you could use Format(value,"MMM").

For example

Column = IF('Table'[date].[Day]<28,FORMAT('Table'[date],"MMM"),FORMAT(EDATE('Table'[date],1),"MMM"))

And the result as below

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

No, it does not make sense to me at least. Having an issue understanding this from what you have described. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

@Pmorg73 ,Can you share sample data and sample output.

[Pmorg73]

I dont want to upload the current PBIX as it is company sensitive. I will try to find time today to thin it out for this query.

Greg. I was not sure if I explained myself very well tbh. An anology that may explain it. I often have to start my year in April to April for Fyear. I wondered if I could have an "invoicing" month that runs 28th of one month to 28th of the next month. 

The reason is that I am doing a projection of the Work in progress value to the end of the month. This works but it is distorted because invoicing takes place on the 28th.