Forum Discussion

bilingual's avatar
bilingual
Icon for Helper V rankHelper V
6 years ago

SAMEPERIODLASTYEAR - when using with Power BI Date table , it adds future years

Hi, i have used SAMEPERIODLASTYEAR  with no problems, before, but when i use it with the date table created in Power BI, somehow i get the 2020 results replicated in 2021, despite there is no data ye...
Need Help
v-yingjl's avatar
v-yingjl
Icon for Community Support rankCommunity Support
6 years ago

Hi bilingual ,

How did you create the Calendar date table? Could you please consider sharing some screenshots about this issue for further discussion?

Or you can try to use Calendartauto() to create a date table, create relationship with the fact table and use sameperiodlastyear() function to test.

 

Best Regards,
Yingjie Li

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

  • bilingual's avatar
    bilingual
    Icon for Helper V rankHelper V
    6 years ago

    Hej Yingjie, this is my Date Calender(Danish)

    Kalender =
    VAR MinYear = YEAR ( MIN ( Data[Dato] ) )
    VAR MaxYear = YEAR ( MAX ( Data[Dato] ) )
    RETURN
    ADDCOLUMNS (
    FILTER (
    CALENDARAUTO( );
    AND ( YEAR ( [Date] ) >= MinYear; YEAR ( [Date] ) <= MaxYear )
    );
    "Dato for heltal"; FORMAT ( [Date]; "YYYYMMDD" );
    "År"; YEAR ( [Date] );
    "Månedsnummer"; FORMAT ( [Date]; "MM" );
    "År/måned tekst"; FORMAT ( [Date]; "YYYY/MM" );
    "År/måned"; FORMAT ( [Date]; "YYYY/mmm" );
    "Månedsnavn_kort"; FORMAT ( [Date]; "mmm" );
    "Månedsnavn_UK"; FORMAT ( [Date]; "mmmm" );
    "Ugenr"; WEEKNUM([Date];2);
    "Ugedag"; FORMAT ( [Date]; "dddd" );
    "Ugedag forkortet"; FORMAT ( [Date]; "ddd" );
    "Kvartal"; "Q" & FORMAT ( [Date]; "Q" );
    "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" )
    )