creating date/calendar table

[Anonymous]

Hi all, 

need some advice here, i am trying to follow the steps here

https://www.powerobjects.com/blog/2018/12/19/sorting-data-fiscal-year-power-bi/

but got this error: The following syntax error occurred during parsing: Invalid token, Line 3, Offset 10, “.

Can anyone advice

[v-xicai]

Hi @Anonymous  ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may need to change the syntax "– 1" to "-1" in your formula in English input format, then it will work well.

FYMonthNum = 
VAR FYStartMonth = 4 
//Update the fiscal year starting month above *Use number between 1 to 12 

RETURN IF ( MONTH ( 'Date'[Date]) >= FYStartMonth, 
MONTH ( 'Date'[Date] )- ( FYStartMonth -1 ), 
12 + (MONTH ( 'Date'[Date] ) - (FYStartMonth -1 ) ) )

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous I copied the code and it worked, I need to fix the double quotes. Can you copy DAX expression what you are using?

[Anonymous]

@parry2k changed the double quotes and it worked!

how abt step 2? I got error as well

[parry2k]

@Anonymous can you share what expression are you using? I have no idea what is step 2.

[Anonymous]

@parry2k 

I copied this code

FYMonthNum = 

          VAR FYStartMonth = 4

          //Update the fiscal year starting month above *Use number between 1 to 12

          RETURN

IF (

                    MONTH ( 'Date'[Date]) >= FYStartMonth,

                    MONTH ( Date[Date] )

– ( FYStartMonth – 1 ),

12

+ (

                         MONTH ( Date[Date] )

– ( FYStartMonth – 1 )

)

)

[parry2k]

@Anonymous you are missing single quote

FYMonthNum = 

          VAR FYStartMonth = 4

          //Update the fiscal year starting month above *Use number between 1 to 12

          RETURN

IF (
                   MONTH ( 'Date'[Date]) >= FYStartMonth,
                   MONTH ( 'Date'[Date] )
- ( FYStartMonth – 1 ),
12
+ (
                        MONTH ( 'Date'[Date] )
– ( FYStartMonth – 1 )
)

)

[Anonymous]

@parry2k 

Strange, i copied yours and still got error at line 12.

[parry2k]

there is special character

fixed with this

FYMonthNum = 
    VAR FYStartMonth = 4

    //Update the fiscal year starting month above *Use number between 1 to 12
    RETURN
IF (
    MONTH ( 'Date'[Date]) >= FYStartMonth,
    MONTH ( 'Date'[Date] )- ( FYStartMonth – 1 ),
    12 + (MONTH ( 'Date'[Date] ) - (FYStartMonth – 1 )
)
)

[Anonymous]

@parry2k i copied and still get error?

Strange 

[parry2k]

@Anonymous  
I would recommend manually type the code, it works for me and I don't see any reason why it shouldn't work for you.