Converting from Month Name and Year to Date

[dfisher373]

I am a complete newby at Power BI so please excuse the simplicity of this post.

I have a table as follows:

Name  |   Month    |   Year   |    Sales Volume

Joe         March         2018        5000

I am trying to measure sales by Month. In addition, I'd like to be able to tabulate quarterly sales, YTD, etc. How do I setup a new column that tabulates the date from the Month and Year columns so that I can use it properly in Power BI? It is end of month data but I do not have the specific end of month dates.

Also, can anyone recommend a good book or website for a beginning user of Power BI that also shows how to properly setup data in Excel to make it easy to analyze?

By the way, I tried to upload the Excel file to this post but was unable to find an attach button. How do I upload files when I submit a question?

Thank you!

[Ashish_Mathur · Accepted solution]

Hi,

To compute sales in different time periods, you must use Date and Time Intelligence functions such as SAMEPERIODLASTYEAR(), PREVIOUSMONTH(), DATESYTD() etc.  These functions work only when you have an actual date column in your source datasets.  So in your Sales data table, you will have to create this calculated column formula to generate a date from the Year and Month

=1*("1/"&Sales[Month]&"/"&Sales[Year])

Format this as a Date and give this column a title, say Date.  As a next step, create a Calendar table by using the following formula (Modellling > New Table)

=CALENDAR(MIN(Sales{date]),MAX(Sales{date]))

In the Calendar Table, create addition column for Month and Year by ising these calculated column formulas = YEAR(Calendar[Date]) and =FORMAT(Calendar[Date],"mmmm").

Now create a relatioship from the Date column of the Sales table to the Date column of the Calendar Table.  In any visual that you build, drag the year and Month from the Calendar Table.  Now you should be able to use measures such as SAMEPERIODLASTYEAR(), PREVIOUSMONTH(), DATESYTD().

[sansk23]

Hi Ahish. if you could. pl. help look at this. Just rying to have a better understanding on the basics with the date/time calculations and transformations. Also, is there any resource available online wherein one can try out examples and learn more on analysis involving dates?

br,

//sansk

[Ashish_Mathur]

Hi,

The Date function will defeinitely not work because the second input into that function should be a number (not text).  The second one appears correct and should work.  Share the link from where i can download your PBI file.

[sansk23]

Hi Ashish. Pls. find below the link. Hope this is completely secure. 

https://drive.google.com/file/d/1WuDWeh-_W6tuPFcbH96NbbpwWsTvNSJp/view?usp=sharing

Br,

//sansk

[Ashish_Mathur]

Hi,

Try this calculated column formula

Date = 1*("1/"&'date'[Month]&"/"&'date'[Year])

Hope this helps.

[sansk23]

Thx. Ashish for your quick help.

If you could pls. help understand the reason for prefix as "1*("

Also , if you could. pls. help with the below expresions - if these will alo fetch the same resuts. 

Getting some errors on trying this. 

= Date.FromText([Month] & " 1," & Number.ToText([Year]))

= Table.AddColumn(Source, "Custom", each Date.FromText([Month] & " 1," & Number.ToText([Year])))

br,

//sank

[Ashish_Mathur]

Hi,

You are welcome.  The 1* converts the "Date formatted as text" into an actual date entry.  You may read up on the Date.FromText from here.

[dfisher373]

Thanks so much for your help. Do you have any suggested reading material or tutorials for someone who is just getting started with Power BI?

Thanks!

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

Well, I would create columns like these:

MonthNum = SWITCH([Month],"January",1,"February",2,"March",3)

Date = EOMONTH(DATE([Year],[MonthNum],1),0)