Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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.
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |