undefinedFiscal period in Direct mode vs Import file

[shahinnakhai]

import report

direct report

Hello,

thank you for the class. I want to create a report on the direct mode from SQL and calculate employment status of each month. I can easily do that on import mode with adding Fiscal Period on my data section or write

the M code (Fiscal Period = LEFT(LaborRoster[FISCPER],FIND("0",LaborRoster[FISCPER],1)+2) & "/" & RIGHT(LaborRoster[FISCPER],2)& "/01" )

but when it comes to Direct none of those work and it says Power BI doesn't support for direct mode. Also for M code there is something wrong for FIND function that give me error that I changed it to ( FIND("0", LaborRoster[FISCPER],1,0)+2) ) that is still wrong and doesn't show the name just date.

can you please help me on that?

Or give me an idea to make it like import file.

here is snapshots of different I get on the report

Thank you

[v-lid-msft]

Hi @shahinnakhai ,

If the FISCPER column is like 9999001/2019, we can create a calculated column using following dax to meet your requiremetn:

Fiscal Period = 
RIGHT (
    'LaborRoster'[FISCPER],
    LEN ( [FISCPER] ) - FIND ( "/", 'LaborRoster'[FISCPER], 1, 0 )
) & "/"
    & RIGHT ( LEFT ( [FISCPER], FIND ( "/", 'LaborRoster'[FISCPER], 1, 0 ) - 1 ), 2 ) & "/01"

Best regards,

[shahinnakhai]

thank you for your help but still it came with number not name letter

[v-lid-msft]

Hi @shahinnakhai ,

Please try to create the following calculated column and format it as the "Date" and "MMMM d":

Fiscal Period Date = 
DATE ( 
    RIGHT ( 'LaborRoster'[FISCPER], LEN ( [FISCPER] ) - FIND ( "/", 'LaborRoster'[FISCPER], 1, 0 )),
    RIGHT ( LEFT ( [FISCPER], FIND ( "/", 'LaborRoster'[FISCPER], 1, 0 ) - 1 ), 2 ),
    1
)

If it does not work, please use the following version:

Fiscal Period Date = 
DATE ( 
    VALUE(RIGHT ( 'LaborRoster'[FISCPER], LEN ( [FISCPER] ) - FIND ( "/", 'LaborRoster'[FISCPER], 1, 0 ))),
    VALUE(RIGHT ( LEFT ( [FISCPER], FIND ( "/", 'LaborRoster'[FISCPER], 1, 0 ) - 1 ), 2 )),
    1
)

Sorry for that we can not share the sample pbix file because it is using DirectQuery Mode.

Best regards,

[shahinnakhai]

Unfortunately didn't work. It is going to name it as Jan and Feb but the issue is Power BI doesn't take Fiscal period as date with hierarchy of date with year, quarter, month and day. and Hierarchy doesn't get populated.

import

Direct

Thanks

[v-lid-msft · Accepted solution]

Hi @shahinnakhai ,

We can create a Hierarchy using following calculated colmun after we format the calculated column Fiscal Period as a Date:

Fiscal Period Year = YEAR('LaborRoster'[Fiscal Period])

Fiscal Period Quartar = "Qtr " & QUARTER('LaborRoster'[Fiscal Period])

Fiscal Period Month =
SWITCH (
    MONTH ( 'LaborRoster'[Fiscal Period] ),
    1, "January",
    2, "February",
    3, "March",
    4, "April",
    5, "May",
    6, "June",
    7, "July",
    8, "August",
    9, "Sepember",
    10, "October",
    11, "November",
    12, "December"
)

Fiscal Period Day = DAY('LaborRoster'[Fiscal Period])

Best regards,