Measure returning blank in a table but has value in total

[user_irl234]

Hi,

I am trying to calculate a metrics for last fiscal year using DAX . Since my fiscal year keeps moving by a day or two, sameperiodlast year isnt returning the correct value. I have a sales fact table and a date table . My date table has column like date_id, calendar_month_id, fiscal_month_id, calendar_year_id, fiscal_year_id and also there are separate tables for calendar and fiscal month and year.

I am using the below DAX to calculation total customers last year

Total Customers LY =

CALCULATE(

    [Total Customers],

    dat_date[dat_year_id]=SELECTEDVALUE(dat_date[fiscal_year_id])-1

)

Its returning correct value for year and also for months in KPIs but is returning blank when used in table visual . Even in table visual it shows the correct value in total column.

Any idea why I am getting this issue here. I would like the measure to display the correct value in table along with total.

[Anonymous]

Thank you bhanu_gautam and dharmendars007 

Hi, @user_irl234 

Based on the information you provided, I have created this table below:

The relationship is shown in the following diagram:

I use the following DAX expression to find the total number of customers for the previous year:

Total Customers LY = 
CALCULATE(
    [Total Customers],
    FILTER(
        ALL('dat_date'),
        dat_date[calendar_year_id] = MAX(dat_date[fiscal_year_id]) - 1
    )
)

The reason why you are blank is that in the context you provided dat_year_id and dat_month_desc are blank because there is no matching data based on the DAX expression conditions you provided. You can adjust accordingly based on the example PBIX file I provided, along with the DAX expression, to show the correct results.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

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.

[user_irl234]

Hi,

Thanks for getting this here. The pbix file does not show correct results against months eg. for Jan 23, it shows last year as 3 but it should be 2 based on data. This seems because the DAX is incorrect and just sets filter on fiscal_year. 

I think I have found why I am getting it blank. In the month table , if I sort the month name by month_id which is used to join the month and date table, I get it as blank. If I dont sort the month_name, i get correct value or if I sort the month_name by any other column, I get corret value. So the issue seems to be with how the query is being translated for the processing engine where it messes up if the column used as join is used for sorting. 

[Anonymous]

Hi, @user_irl234 

You find a solution, you can mark your response as a solution so that others in the community can quickly find a solution if they encounter a similar problem.
Your cooperation will work with us to promote the development of the community, and the solutions you share will enable the community to help more people.

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.

[user_irl234]

Hi, it seems like a bug as I need the months to be sorted by month_id . I updated the pbix file which you shared by sorting month_name to create the same scenario I had. I cant find an option to attach the file here but you can just put a sort and check.

Measure I am running is 

Total Customers LY =

CALCULATE(

    [Total Customers],

    dat_date[fiscal_year_id]=SELECTEDVALUE(dat_date[fiscal_year_id])-1

)

If I dont sort the month_name, the same measure returns correct value. Since its not logical that sorting should mess up the DAX this is why I feel there is some bug. I have a workaround where I can another duplicate of month_id and sort the name on this field but this will mean I am duplicating data in the model which is not ideal. 

[Anonymous]

Hi, @user_irl234 

Thank you very much for your reply, I am also interested in your question, can you share the PBIX file containing this issue via OneDrive so that I can check it further.
Please be careful not to include any sensitive information.

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.

[user_irl234]

Hi , can you please share the source excel file that you are using.

[Anonymous]

Hi, @user_irl234 

Thank you very much for your reply. Here's my dataset:

date_idcalendar_month_idfiscal_month_idcalendar_year_idfiscal_year_id

20230101	202301	202301	2023	2023
20230102	202301	202301	2023	2023
20230103	202301	202301	2023	2023
20230104	202301	202301	2023	2023
20230105	202301	202301	2023	2023
20220101	202201	202201	2022	2022
20220102	202201	202201	2022	2022
20220202	202202	202202	2022	2022

sales_iddate_idcustomer_idsales_amount

1	20230101	101	100
2	20230102	102	150
3	20230103	103	200
4	20230104	104	250
5	20230105	105	300
6	20220101	106	400
7	20220102	107	500
8	20220202	108	600

calendar_month_idcalendar_month_name

202301	1/1/2023
202302	2/1/2023
202201	1/1/2022
202202	2/1/2022

I sorted the month names in the two month tables by moth ID according to your description:

Then create a new measure as follows:

Measure = 
CALCULATE(
    [Total Customers],
    'dat_date'[fiscal_year_id] = SELECTEDVALUE(dat_date[fiscal_year_id]) - 1
)

Create a table visual with the following fields that will arrive at the correct value:

If I change the measure to what it looks like in your original post:

Measure = 
CALCULATE(
    [Total Customers],
    'dat_date'[calendar_year_id] = SELECTEDVALUE(dat_date[fiscal_year_id]) - 1
)

When I put the 'dat_date' [calendar_year_id] column in the date table into the table, I get the correct result, and the total is not displayed. The main reason for this is that the selectedvalue function gets a null in total, which is empty because there is no null value in my data table.

Under this measure, if we put fiscal_year_id column into the table, we get the blank value. The main original of our filters overlays, resulting in blanks:

When we change the selectedvalue to the max function, we get the correct value and total:

Measure = 
CALCULATE(
    [Total Customers],
    'dat_date'[calendar_year_id] = MAX(dat_date[fiscal_year_id]) - 1
)

I've uploaded the PBIX file I 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.

[user_irl234]

Hi, thank you for preparing this scenario, it seems you did not put an year filter. If you put an year slicer from fiscal year in the date table, the values turn blank in the pbix file that you shared.

[Anonymous]

Hi, @user_irl234 

You should follow the DAX calculation logic in the image below to see how it returns a blank:

When I put fiscal_year_id into the table, I do get blanks, but this is the result of the comprehensive filter:

Here's how it calculates, and the same works for returning blanks for other rows:

For the same field, the external filter (row label) is overridden in the second parameter of calculate. The different fields will join the relationships they use, after the fiscal_year_id fields have been added. The total customer number when fiscal_year_id=2023 and calendar_year_id=2022 is satisfied.

In this graph, the override behavior occurs because the filter for the second parameter calculates overrides the outer filter row labels (which are the same column). The coverage result is calendar_year_id=2022. Plus the external filter month returns the correct count: 2.

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.

[user_irl234]

Hi, thank you for looking at it. I had removed calendar year from the measure below and changed it to fiscalyear because the below measure is logically incorrect. Calendar year cannot be compared to fiscal year. Both have different dates and the fiscal year keeps changing . 

Can you please follow these steps and see if it still return a blank for you as it does for me.

1- Use this measure or equivalent 

Total Customers LY =

CALCULATE(

    [Total Customers],

    FILTER(

        ALL('dat_date'),

        dat_date[fiscal_year_id] = MAX(dat_date[fiscal_year_id]) - 1

    )

)

2 - Sort month name in the month table by month_id

3 - Put a fiscal_year slicer and select a year

For me it returns a blank . Sorry I couldnt use your measure as you compared financial year with calendar year which cant be done .

[Anonymous · Accepted solution]

Hi, @user_irl234 

Thank you very much for your reply. Based on your description, I use the following DAX expression:
Clear the fiscal year filter in the Filter and keep the other filters in the date table, so that the number of customers in the previous year for the fiscal month can be correctly calculated:

1.

Total Customers LY = CALCULATE(
    [Total Customers],
    FILTER(
        ALL('dat_date'[fiscal_year_id]),
        dat_date[fiscal_year_id] = MAX(dat_date[fiscal_year_id]) - 1
    )
)

 2.I sort the fiscal months using monthID:

3.Put a fiscal_year slicer and select a year

You can see that you can find the correct result. I've attached the PBIX file for 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.

[user_irl234]

Hi, unfortunately I dont have permission to share anything on onedrive as I am using the client account. But you can use the measure above and just sort the month_name by month_id in the month table and you will see the results.

[dharmendars007]

Hello @user_irl234 , 

Its returning blank in tabe becasue there is Filter contect in but in KPI card there is no filter context like you see "2024 march", You can try the below measure if it works..

Total Customers LY =
CALCULATE(
[Total Customers],
DATEADD(dat_date[date_id], -1, YEAR))

If you find this helpful, please mark it as solution and Your Kudos are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[user_irl234]

Thanks for your response. I cant used DATEADD function as the fiscal year keeps moving so some dates in last 12 month would be a part of different fiscal year. My only option is to use filter on year to have it as (selected year -1 ) as there is fiscal year column. 

[bhanu_gautam]

@user_irl234 , Try using

DAX
Total Customers LY =
SUMX(
VALUES(dat_date[dat_year_id]),
CALCULATE(
[Total Customers],
dat_date[dat_year_id] = SELECTEDVALUE(dat_date[fiscal_year_id]) - 1
)
)

[user_irl234]

Hi Bhanu,

Thanks for your response, I am still getting blank in the table and value in the total as shown in the screenshot.