Forum Discussion

ajdm2007's avatar
ajdm2007
Icon for Helper III rankHelper 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 t...
General Comment
Show and Tell
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
Icon for Helper III rankHelper 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.