Show date if filters are met

[Anonymous]

Hello all, 

I have multiple months worth of data, where I'm hoping users will filter between themselves however I want to ensure they are looking at the most up to date information (e.g. if they filter to month 3 then all figures in file 3 will be shown). I'm hoping to sum/calculate/show a column value based on set filters and I've been trying to use the below formulas:

Measure 1 = Calculate([Table 1 Date Column], Filter('Table 2', 'Table 2[Month]<=[Current Month]))

Measuer 1 = Calculate(Sum([Table 1 Date Column]), Filter('Table 2', 'Table 2[Month]<=[Current Month]))

Both of these don't seem to work. The issue I have is the column is formatted to text (which I know can't be calculated or summed) and is in the current format:

15/10/20

12/6/20

etc. etc.

So I thought the following formula might work, because I'm basically wanting a column to be copied if the filters are met:

Measure 1 = IF(['Table 1 Date Column'], Filter('Table 2', 'Table 2'[Month]<=[Current Month]))

The problem with the above formula is that I can't seem to find Table 1 at all because it's not already a measure. Is there something I'm missing in the above formula please?! Sorry, reletively new at writing my own formulas out. 

Thanks!

[Icey]

Hi @Anonymous ,

It will be best to share us your .pbix file. Then we will better understand the issue you are experiencing and conduct specific tests.

Please remove sensitive information. It is suggested to upload your file to OneDrive for Business and then paste the link here.

Best Regards,

Icey

[Anonymous]

Hi, 

Thank you for the reply, sadly I can't share my .pbix however I hope I can explain it a little better below:

Relationships:

Table 1 - Table 2 (Many to One, Single)

Table 1 - Table 3 (Many to One, Single)

Table 4 has no relationships

I figured that Power BI couldn't deal with my column because they were all dates so I followed the steps below to try to get around this but keep getting an error:

- Duplicated date column and changed format to number

- Measure 1 = CALCULATE(SUM('Table 1'[Date Number]),Filter('Table 3', 'Table 3'[Month Order]=[Current Month Value From Table 4]), Filter('Table 2','Table 2'[Year Order]=max'Year'[Year Parameter])))

- Measure 2 = CALCULATE([Measure 1], Filter('Table 2', 'Table 2'[Month Order]<=[Current Month Value From Table 4]))

- Measure 3 = Convert([Measure 2], DATETIME)

- Measure 4 = MAXX(FILTER('Table 1', 'Table 1'[Month]<=[Current Month Value From Table 4]), 'Table 1'[Measure 3]

This all seems to work for a split second and then I get the following message:

"MdxScript(Model) (5, 80) Calculation error in measure 'Table 1'[Measure 4]: DAX comparisons operations do not support comparing balues of type Text with values of type Integet. Consider using the VALUE or FORMAT function to convert one of the values"

I colour coded my column formatting above with the key below:

Text/General

Date

Number/Whole Number

I hope this makes a little more sense! I should say that when I tested whether my measures work in a visual I seem to fall at the first measure.

I'm not happy with that last measure as I'm not sure it's needed and I feel that 4 measures is going around the houses a little. Is there perhaps a simplier way of doing this?? I've been looking at the FORMAT function this morning. 

Thank you.

[Icey]

Hi @Anonymous ,

Sorry to reply late. 

Could you show me some sample data with the sample structure of your real data and the desired result? Please give a simple example. I am a little confused.🤔

Best regards

Icey

[Anonymous · Accepted solution]

Hi, 

Thank you very much for replying and sorry for confusing you. My customer has decided to move away from this idea and we have moved on to something far more simple. I wasn't sure how to close this thread down though?

Kind regards, 

E

[Icey]

Hi @Anonymous ,

Sorry for not being able to help you.

And for closing this thread, please accept your reply as a solution to make this post "Solved".😊

Best regards

Icey

[Anonymous]

What I've done to get around this is to:

a) format the column to date

b) create a measure with the following formula: Training Current = MAX('Table1'[Date Column])

This seems to work for the most part however if I filter down by month this doesn't work because if a date changes in say month 5 and I filter back to month 3 then the figure will still be month 5's figure and not month 3. So a little more thinking to do.

Can a MAX function used with a Filter function?