Metrics in blank

[ajdm2007]

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,

[Anonymous]

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]

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]

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.

[Idrissshatila]

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]

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.

[ajdm2007]

Hello @Idrissshatila,

I went through the option 1, but everything remained unchanged.

Any advise?