Forum Discussion
Invoicing Date Month value
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?
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.
4 Replies
Pmorg73 ,Can you share sample data and sample output.
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.
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
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.
