Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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?    ...
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    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.

v-yingjl's avatar
v-yingjl
Community Support
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
    v-yingjl
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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