Month Measure

[Anonymous]

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

[v-yingjl]

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.

[Anonymous]

Hi,

I'm using a DirectQuery for my model so I cant use the below expression for my solution:

Month = FORMAT('Table'[BookedDate],"mmm")

Kindly suggest anything else which i can do in my DirectQuery model.

Regards,

Himanshu

[v-yingjl · Accepted solution]

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.

[Anonymous]

I actually came with the below solution:

Created a new column with the below expression then sorted it by Month.

Month Name = SWITCH(Deals[Month],1,"Jan",2

,"Feb",3,"Mar",4,"April",5,"May",6,"June",7

,"July",8,"Aug",9,"Sep",10,"Oct"

,11,"Nov",12,"Dec")

Regards,

Himanshu

[amitchandak]

@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/421045

Check Settings
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time

[sanalytics]

@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

[Anonymous]

The hierarchy doesn't have month.

[amitchandak]

@Anonymous , 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