Forum Discussion

Peddabomma's avatar
Peddabomma
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago
Solved

Current Fiscal Week Vs Last Year Fiscal Week

Hi All, I am trying to compare current fiscal week with last year same fiscal week . I cannot use sameperiodlastyear as my fiscalweek column is not a date column.

 

Help Appreciated.

 

Thanks,Raghu

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Peddabomma,

     

    In this scenario, you may not be able to use the standard DAX time intelligence functions(i.e. sameperiodlastyear). However, you should be able to use some custom formulas like below to do calculations for Last Year Fiscal Week. :smileyhappy:

    Last Year Fiscal Week Sales =
VAR currentFiscalYear =
    MAX ( 'Table1'[Fiscal Year] )
VAR currentFiscalWeek =
    MAX ( 'Table1'[Fiscal Week] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Fiscal Year]
                = currentFiscalYear - 1
                && 'Table1'[Fiscal Week] = currentFiscalWeek
        )
    )

     

    Regards

2 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Peddabomma,

     

    In this scenario, you may not be able to use the standard DAX time intelligence functions(i.e. sameperiodlastyear). However, you should be able to use some custom formulas like below to do calculations for Last Year Fiscal Week. :smileyhappy:

    Last Year Fiscal Week Sales =
VAR currentFiscalYear =
    MAX ( 'Table1'[Fiscal Year] )
VAR currentFiscalWeek =
    MAX ( 'Table1'[Fiscal Week] )
RETURN
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Fiscal Year]
                = currentFiscalYear - 1
                && 'Table1'[Fiscal Week] = currentFiscalWeek
        )
    )

     

    Regards