Extract year and month for the KPI

[deepak_km9886]

I have attached to files, how to get the  year and month like Jan 2018 .

I used the following ,  but i get hyphen as a result

yearmonth file is having date formats of kind yyyy-mm and YY/DD/M

Year(Date(date#(subfield([Invoice Date],' ',1),'MM/DD/YYYY')))

Year(Date#(YearMonth, 'YYYY-MM'))

Both dint yield any result

[Anonymous]

Hi!

Use Alt and MonthName functions together.

-RC

[petter · Accepted solution]

LOAD

   Date(Date#([Invoice Date'],'MM/DD/YYYY'),'MMM YYYY') AS YearMonth

FROM

  LIB://DATA/Invoice.xlsx (ooxml........ );

This will keep the underlying day of the month but display only the month and year part. If you want a date that always is the first of the month you will have to add MonthStart() like this:

   Date(MonthStart(Date#([Invoice Date'],'MM/DD/YYYY')),'MMM YYYY') AS YearMonth

LOAD

   Date( If( Index( YearMonth ,'-' ) , Date#(YearMonth&'-01','YYYY-MM-DD') , Date#(YearMonth, 'YY/DD/M') ) ,'MMM YYYY') AS YearMonth

FROM

  LIB://DATA/YearMonth.xlsx (ooxml.....);

Here you probably want the YearMonth to always start at the first of any given month:

   Date( MonthStart(If( Index( YearMonth ,'-' ) , Date#(YearMonth&'-01','YYYY-MM-DD') , Date#(YearMonth, 'YY/DD/M'))) ,'MMM YYYY') AS YearMonth

[renuka_sasikumar]

Hi,

I used this

LOAD
[Invoice Date],
MonthName(Floor([Invoice Date])) as MonthName
FROM
[...\Testing\Invoice.xlsx]
(ooxml, embedded labels, table is Sheet1);

I am getting the output as

Regards,

Renuka S