Forum Discussion

epang's avatar
epang
Icon for Advocate I rankAdvocate I
1 month ago
Solved

Table import issues and Date measure creation

I have a data table from the warehouse. after the transformation, in the data pane, the date field and other signs are not displaying. I cannot use the Date field. How can I resolve this? I have create a separate date table. 

 

See the below image for the table and the code for Date table. 

What I want to achieve is to get the % change Month by month, % change quarter by quarter and % change year by year. 

 

 

 

Dim_Date =
VAR StartDate = DATE(2020,1,1)
VAR EndDate = DATE(2030,12,31)

RETURN
ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),

    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Month Short", FORMAT([Date], "MMM"),

    "Quarter Number", QUARTER([Date]),
    "Quarter", "Q" & QUARTER([Date]),

    "Year-Month", FORMAT([Date], "YYYY-MM"),
    "Year-Quarter", YEAR([Date]) & "-Q" & QUARTER([Date]),

    "Day Number", DAY([Date]),
    "Day Name", FORMAT([Date], "DDDD"),
    "Day Short", FORMAT([Date], "DDD"),

    "Week Number", WEEKNUM([Date])



)
Show and Tell
Tips and Tricks
  • ShahRukhSameer's avatar
    ShahRukhSameer
    1 month ago

    Hi epang, 

     

    It looks like your Date table is fine, so the first thing I'd check is whether the date column in your fact table is actually set to Date/DateTime after the transformation. If Power BI sees it as Text, the date hierarchy won't appear and time intelligence measures won't work correctly.

    Also make sure you've:

    • Marked Dim_Date as a Date Table using the [Date] column.
    • Created an active relationship between DimDate[Date] and the date field in your fact table.
    • Disabled Auto Date/Time only if you're intentionally using your own calendar table.

      Once the relationship is working, you can create measures like:

      Cases = COUNTROWS(FactTable)
       
      % MoM =
      DIVIDE(
      [Cases] - CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, MONTH)),
      CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, MONTH))
      )
       
      % QoQ =
      DIVIDE(
      [Cases] - CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, QUARTER)),
      CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, QUARTER))
      )
       
      % YoY =
      DIVIDE(
      [Cases] - CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, YEAR)),
      CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, YEAR))
      )

       

      If the hierarchy still isn't showing, can you confirm the data type of the source date column and whether the relationship to Dim_Date is active? That's usually where the issue turns out to be.

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    1 month ago

    epang 

    try to set the data type to date in power query (Choose Transform → Data type → Date.) to see it works or not.

     

    If you get error message that mean there is any wrong data in the column. You need to fix this first.

    Below is the dim table that you can use to create

    Date =
    VAR MinimumDate =
    MINX ( ALL ( 'FactTable' ), 'FactTable'[TransactionDate] )
    VAR MaximumDate =
    MAXX ( ALL ( 'FactTable' ), 'FactTable'[TransactionDate] )
    RETURN
    ADDCOLUMNS (
    CALENDAR (
    DATE ( YEAR ( MinimumDate ), 1, 1 ),
    DATE ( YEAR ( MaximumDate ), 12, 31 )
    ),
    "Year", YEAR ( [Date] ),
    "Year Month", FORMAT ( [Date], "YYYY-MM" ),
    "Year Month Sort", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Month Number", MONTH ( [Date] ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "Year Quarter",
    FORMAT ( [Date], "YYYY" ) & "-Q" & FORMAT ( [Date], "Q" ),
    "Year Quarter Sort",
    YEAR ( [Date] ) * 10 + QUARTER ( [Date] )
    )

     

     

    Previous Year Value =
    CALCULATE (
    [Total Value],
    DATEADD ( 'Date'[Date], -1, YEAR )
    )

     

    OR

     

    Previous Year Value =
    CALCULATE (
    [Total Value],
    DATEADD ( 'Date'[Date], -1, YEAR )
    )

     

    YoY Change % =
    DIVIDE (
    [Total Value] - [Previous Year Value],
    [Previous Year Value]
    )

     

     

    if this does not work, pls provide some sample data and expected output

  • Praful_Potphode's avatar
    Praful_Potphode
    Icon for Super User rankSuper User
    1 month ago

    Hi epang ,

     

    Please try the option suggested by ryan_mayu .

    Mostlikely this lookslike datatype issue.Once it is resolved you can create time intelligence functions.PFA sample pbix for time intelligence.

    Another approach you can take is create date table in mquery.

    Please go through below articles:

    https://pragmaticworks.com/blog/creating-a-date-dimension-with-power-query 

    https://www.mssqltips.com/sqlservertip/6756/power-bi-calendar-table/ 

    https://gorilla.bi/power-query/date-table/ 

     

    you can make these tables dynamic using power query above

    Please give kudos or mark it as solution once confirmed,

     

    Thanks and Regards,

    Praful

     

     

     

  • ShahRukhSameer's avatar
    ShahRukhSameer
    Icon for Continued Contributor rankContinued Contributor
    1 month ago

    Hi epang, 

     

    It looks like your Date table is fine, so the first thing I'd check is whether the date column in your fact table is actually set to Date/DateTime after the transformation. If Power BI sees it as Text, the date hierarchy won't appear and time intelligence measures won't work correctly.

    Also make sure you've:

    • Marked Dim_Date as a Date Table using the [Date] column.
    • Created an active relationship between DimDate[Date] and the date field in your fact table.
    • Disabled Auto Date/Time only if you're intentionally using your own calendar table.

      Once the relationship is working, you can create measures like:

      Cases = COUNTROWS(FactTable)
       
      % MoM =
      DIVIDE(
      [Cases] - CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, MONTH)),
      CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, MONTH))
      )
       
      % QoQ =
      DIVIDE(
      [Cases] - CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, QUARTER)),
      CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, QUARTER))
      )
       
      % YoY =
      DIVIDE(
      [Cases] - CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, YEAR)),
      CALCULATE([Cases], DATEADD(Dim_Date[Date], -1, YEAR))
      )

       

      If the hierarchy still isn't showing, can you confirm the data type of the source date column and whether the relationship to Dim_Date is active? That's usually where the issue turns out to be.
  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    29 days ago

    HI epang​,

    Checking in to see if your issue has been resolved. let us know if you still need any assistance.

    Thank you.