Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Don't summarize year - in script

When creating a date table, how do I tell Power BI not to summarize years:        
Need Help
mh2587's avatar
mh2587
Icon for Super User rankSuper User
4 years ago

Just go Data Model select column and the upper left corner change the data type to date 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    That doesn't work.... as it is a script ... 

    • mh2587's avatar
      mh2587
      Icon for Super User rankSuper User
      4 years ago

      Can you please share the pbix file

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I don't know to upload the .pbix file... but here is the code

         

        Table =
        VAR BaseCalendar = CALENDAR ( DATE ( 2015, 7, 1 ), DATE ( 2026, 6, 30 ) )
        RETURN
        GENERATE (
        BaseCalendar,
        VAR BaseDate = FORMAT([Date],"Short Date")
        VAR CY = YEAR ( BaseDate )
        VAR MonthName = FORMAT ( [Date], "mmmm" )
        VAR MonCY = FORMAT ( [Date], "mmm-yy" )
        VAR FY = IF(MONTH([Date]) <7, YEAR(BaseDate), YEAR(BaseDate)+1 )
        VAR FYQtr = SWITCH( MONTH([Date]),
        1, "Q3",
        2, "Q3",
        3, "Q3",
        4, "Q4",
        5, "Q4",
        6, "Q4",
        7, "Q1",
        8, "Q1",
        9, "Q1",
        10, "Q2",
        11, "Q2",
        "Q2" )

        RETURN ROW (
        "Day", BaseDate,
        "Year", CY,
        "Month", MonthName,
        "Year Month", MonCY,
        "FY", FY,
        "FYQtr", FYQtr
        )
        )