Latest commentary based on month and country selection

[Ghuiles]

Hello

I think i am struggling with something simple.

I have a table with Comments

Date	Region	Comment
1/09/2019	Europe	Better sales on market A
1/10/2019	Asia	Strong demand on X
1/10/2019	Europe	Ntd

This table is linked to a DimDate table.

The comments are provided only per month.

So, in DAX, how can i show the comments only for the latest month selected?

At the end the user should see either one comment for one region, or if several regions then several comments.
I think that the best object is a table or a matrix.

I tried to build something with MAXDimDate[Date]) or MAX(DimDate[YearMonth]) and VALUES but it did not work.

What i am missing?

Thanks in advance for your help. G.

[v-yuta-msft · Accepted solution]

@Ghuiles ,

Create a measure using dax as below, please refer to the attached pbix file.

Selected Date = IF(CALCULATE(MAX('Dimension'[Date]), ALLSELECTED('Dimension')) = MAX('Fact'[Date]), MAX('Fact'[Date]))

 

Community Support Team _ Jimmy Tao

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

[v-yuta-msft · Accepted solution]

@Ghuiles ,

Create a measure using dax as below, please refer to the attached pbix file.

Selected Date = IF(CALCULATE(MAX('Dimension'[Date]), ALLSELECTED('Dimension')) = MAX('Fact'[Date]), MAX('Fact'[Date]))

 

Community Support Team _ Jimmy Tao

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

[Ghuiles]

Thanks for your proposal! 🙂

hmm. I think we are close to the solution, but still not fully there.

The user is indeed selecting the month via a slicer on DimDate[Month].

DimDate and the Fact tables are linked via the field [Date], so not month.

Somehow it still does not work 😞

No matter which month it is selected (only September, the full year, all months before Sept etc), the result is blank.

I unfortunately cannot post my model.
Any clue on how to solve this?

[Ghuiles · Accepted solution]

Hello!

@v-yuta-msft many thanks for your idea! I managed to build on that with EOMONTH to get the months flattened, and it works now as desired 🙂

At the end the formula looks like: 

IF(CALCULATE(EOMONTH(MAX('DimDate'[Date]),0), ALLSELECTED('DimDate')) = EOMONTH(MAX('Sales Comments'[Date]),0), eoMONTH(MAX('Sales'Comments'[Date]),0))

Cheers. G.

[amitchandak]

When you say a latest selected month. So once filter on a month of your date then you will get that month data.

Can you share sample data and sample output. Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Ghuiles]

Hi!

The problem is that the user can select for example Jan, Feb, March. But we would like to see only the comments related to March.

Best regards. G.