Forum Discussion
Month Measure
Hi Guys,
I have a doubt regarding one of my column.
I have a column "Booked Date" in my report and i have no. of deals against the booked date.
Can i break the booked date into months?
I can certainly do it with a calender table but can I do it with just the booked date? Is there any measure which I can use?
Desired Output:
| Month | ID |
| Jan | 1100 |
| Feb | 1260 |
| March | 1500 |
Regards,
Himanshu
Hi Anonymous ,
If you are using Direct Query mode, you can create a column like this to define month name:
Month = VAR _m = MONTH ( 'Date_ID'[BookedDate] ) RETURN SWITCH ( TRUE (), _m = 1, "Jan", _m = 2, "Feb", _m = 3, "Mar", _m = 4, "Apr", _m = 5, "May", _m = 6, "Jun", _m = 7, "Jul", _m = 8, "Aug", _m = 9, "Sep", _m = 10, "Oct", _m = 11, "Nov", _m = 12, "Dec" )Put this column and [ID] column in the table visual, change the aggreation of [ID] as 'SUM' and rename for this visual:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Anonymous , seems like you do not have a date hierarchy
These are the reasons Date Hierarchy can be missing
https://community.powerbi.com/t5/Desktop/Date-Hierarchy-Doesn-t-show/td-p/525460
https://community.powerbi.com/t5/Desktop/Date-hierarchy-not-available/td-p/438804
https://community.powerbi.com/t5/Desktop/Lost-Missing-Date-Hierarchy/td-p/421045Check Settings
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-timeAnonymous , with default date hierarchy
https://5minutebi.com/2017/11/29/how-to-use-powerbi-date-hierarchy/
Create month year in the table or date table
Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")Sort Month Year on Month Year sort
Anonymous
Make your boooking date as date data type.
Just drag your booking date column then only choose month from hiearchy.then drag you no.of deals measure.
refer the below screenshot
hope it will help..let us know your concern
regards,
sanalytics
Hi Anonymous ,
If your date has no hierarchy, please check whether your source table has any relationship with other tables or whether you have disabled 'Auto date/time for new files' in options and settings.
In addition, if you do not want to use the date hierarchy to achieve this, you can create a calculated column like this:
Month = FORMAT('Table'[BookedDate],"mmm")Put this column and [ID] column in the table visual, change the aggreation of [ID] as 'SUM' and rename for this visual:
Attached a sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Anonymous ,
If you are using Direct Query mode, you can create a column like this to define month name:
Month = VAR _m = MONTH ( 'Date_ID'[BookedDate] ) RETURN SWITCH ( TRUE (), _m = 1, "Jan", _m = 2, "Feb", _m = 3, "Mar", _m = 4, "Apr", _m = 5, "May", _m = 6, "Jun", _m = 7, "Jul", _m = 8, "Aug", _m = 9, "Sep", _m = 10, "Oct", _m = 11, "Nov", _m = 12, "Dec" )Put this column and [ID] column in the table visual, change the aggreation of [ID] as 'SUM' and rename for this visual:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
