Forum Discussion

ajdm2007's avatar
ajdm2007
Helper III
2 years ago

Metrics in blank

Hi all,

 

I am trying to use a metric based on two tables (tickets and calendar), but for some reason, the value is returned as blank.

 

 

The total tickets metric is calculated based on the ticket table, which has a date_entered field in the format mm/dd/yyyy hh:mm. This field is related to the date field in the calendar table, but that one is in the format mm/dd/yyyy. I’m unsure if this is the source of the issue.

 

Any advice?

 

Thank you,

General Comment
Show and Tell

6 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello ajdm2007 ,

     

    yes the issue is that the date column in the data table is datetime, you need to change it to date in Power query so it works.

    the change should be in Power query.

    • ajdm2007's avatar
      ajdm2007
      Helper III
      2 years ago

      Hi Idrissshatila,

       

      I thought so, but this presents a challenge because I use the datetime field for multiple metrics, so I should not modify the column. I can think of two options:

      1. Duplicate the column and change the format to date.
      2. Adapt the relationship between the tables so that only the date is considered. I've done this in other programming languages, but I'm not sure if it's possible in this case.

       

      Thanks for your help. I’ll be attentive to your response.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Thank you Idrissshatila

    Hi, ajdm2007 

    When you use the PREVIOUSMONTH function, you need to provide a date context. There is no corresponding context in the card visual, as shown in the following image:

    When I put the date column and measure values ​​of the date table into the table visual object, the calculations are correct:

    If you want to obtain the data one month before the maximum date in the current date table and display it in the card visual, you can create a measure using the following DAX expression:

    Measure = 
VAR _year = YEAR(MAX('Calender'[Date]))
VAR _month = MONTH(MAX('Calender'[Date]))
RETURN
CALCULATE(
    SUM(financials[ Sales]),
    FILTER(
        'Calender',
        MONTH('Calender'[Date]) = _month - 1 && YEAR('Calender'[Date]) = _year
    )
)

    I have included the PBIX file used for this tutorial below.

     

     

    Best Regards

    Jianpeng Li

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

    • ajdm2007's avatar
      ajdm2007
      Helper III
      2 years ago

      Hello,

       

      I'm working with your formula recomended, how ever in my case insteat of sum the sales I need to count the tickets.

       

      Measure = 
VAR _year = YEAR(MAX('Calendar'[Date]))
VAR _month = MONTH(MAX('Calendar'[Date]))
RETURN
CALCULATE(
    [total_tickets],
    FILTER(
        'Calendar',
        MONTH('Calendar'[Date]) = _month - 1 && YEAR('Calendar'[Date]) = _year
    )
)

      Where total_tickets is the result of DISTINCTCOUNT(tickets[id]). But, everything remain unchange.

       

      Another idea?

       

      Thank you, 

       
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, ajdm2007 

        Based on your description, I created a measure for my Product column following the DAX you provided:

        Productcount = DISTINCTCOUNT(financials[Product])

        Put this measure in the measure I mentioned earlier:

        Measure = 
VAR _year = YEAR(MAX('Calender'[Date]))
VAR _month = MONTH(MAX('Calender'[Date]))
RETURN
CALCULATE(
    [Productcount],
    FILTER(
        'Calender',
        MONTH('Calender'[Date]) = _month - 1 && YEAR('Calender'[Date]) = _year
    )
)

        Getting the right results:

        You display blank, you need to check if your date table corresponds to the date of your fact table, if the date column of your fact table has a date format of hours, minutes, seconds, then your date table needs to keep the same format and correspond to it.
        Unless you have no data for the previous month. You can change the DAX above directly with your tickets, as follows:

        UseFActTableDate column = 
VAR _year = YEAR(MAX('financials'[Date]))
VAR _month = MONTH(MAX('financials'[Date]))
RETURN
CALCULATE(
    [Productcount],
    FILTER(
        'financials',
        MONTH('financials'[Date]) = _month - 1 && YEAR('financials'[Date]) = _year
    )
)

        This will not rely on the date table for calculations.

        I've provided the PBIX file used this time below.

         

         

         

        Best Regards

        Jianpeng Li

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