Forum Discussion

ak28121's avatar
ak28121
Frequent Visitor
2 years ago
Solved

Date table flag calculations

I've a calendar table consist of Date,Year column , Now i want to derive below flags under my calendar table
1)Current fiscal year flag
2)previous year flag
3)current fiscal quarter flag,
4)previous fiscal quarter flag,
5)previous fiscal year current quarter flag,
Note- fiscal year starts from Jan and ending in december,

Thanks in advance 🙂

General Comment
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    ak28121 

    pls try this

    year = year('Table'[Date])
     
    quarter = QUARTER('Table'[Date])
     
    currentfiscalyear = if([year]=year(TODAY()),"Y")
     
    previousfiscalyear = if('Table'[year]=year(TODAY())-1,"Y")
     
    currentfiscalquarter = if('Table'[year]=year(TODAY())&&'Table'[quarter]=QUARTER(today()),"Y")
     
    previous fiscal quarter =
     if(QUARTER(today())=1,if('Table'[year]=year(today())-1&&'Table'[quarter]=4,"Y"),
     if('Table'[year]=year(today())&&'Table'[quarter]=QUARTER(today())-1,"Y"))
     
    previous fiscal year current quarter = if('Table'[year]=year(today())-1&&'Table'[quarter]=QUARTER(today()),"Y")
     
     
     pls see the attachment below

1 Reply

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    ak28121 

    pls try this

    year = year('Table'[Date])
     
    quarter = QUARTER('Table'[Date])
     
    currentfiscalyear = if([year]=year(TODAY()),"Y")
     
    previousfiscalyear = if('Table'[year]=year(TODAY())-1,"Y")
     
    currentfiscalquarter = if('Table'[year]=year(TODAY())&&'Table'[quarter]=QUARTER(today()),"Y")
     
    previous fiscal quarter =
     if(QUARTER(today())=1,if('Table'[year]=year(today())-1&&'Table'[quarter]=4,"Y"),
     if('Table'[year]=year(today())&&'Table'[quarter]=QUARTER(today())-1,"Y"))
     
    previous fiscal year current quarter = if('Table'[year]=year(today())-1&&'Table'[quarter]=QUARTER(today()),"Y")
     
     
     pls see the attachment below