undefined

[AtchayaP]

How to show fiscal periods instead of calendar ones in powerbi

[v-yanjiang-msft · Accepted solution]

Hi @AtchayaP ,

According to your description, I create a sample.

1.Create a date table, which has the max and min time of your calendar.

2.Create a new table like this.

Table = 
ADDCOLUMNS (
        CALENDARAUTO ( 1 ),
        
        "CalMonth", FORMAT ( [Date], "mmm" ),
        "CalQtr", "Q"
            & CEILING ( MONTH ( [Date] ), 3) / 3,
        "CalYear", YEAR ( [Date] ),
        "CalWeekNo", WEEKNUM ( [Date], 2 ),
        "Fiscal Qtr", "Q"
            & CEILING ( MONTH ( EDATE ( [Date], - 1) ), 3 ) / 3,
        "Fiscal Year",
        VAR CY =
            RIGHT ( YEAR ( [Date] ), 2 )
        VAR NY =
            RIGHT ( YEAR ( [Date] ) + 1, 2 )
        VAR PY =
            RIGHT ( YEAR ( [Date] ) - 1, 2 )
        VAR FinYear =
            IF ( MONTH ( [Date] ) > 1, CY & "-" & NY, PY & "-" & CY )
        RETURN
            FinYear,
        "Fiscal Month", MONTH ( EDATE ( [Date], - 1) ),
        "Weekend/Working", IF ( WEEKDAY ( [Date], 2 ) > 5, "Weekend", "Working" ),
        "Day", FORMAT ( [Date], "ddd" )
    )

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Samarth_18]

Hi @AtchayaP ,

You can refer the below article:-

https://www.mssqltips.com/sqlservertip/7133/compute-fiscal-week-calculations-with-power-bi-and-dax/ 

It would be helpful to answer if you could share some sample data with expected output.

Thanks,

Samarth