Set Analysis total by month on two dates

[Lou_Lab]

Hello everyone,

I'm looking for some help !

I have a database with 2 dates (expedition date and reception date) by row. I would like to have a table with the total weight received by expedition month but with a display by reception month.

I tried :

Col1 : Monthname(ReceptionDate)

Col 2 : {1<[ExpeditionDate]=p([ExpeditionDate])>}Sum(Total<[ExpeditionDate], [Lot]>[Weight])

But this print the global Weight by fiscal year.

Thanks for your help !

An

[Mike_Dickson]

Hello @Lou_Lab 

Given that you want a table with the total weight received by "expedition month" but displayed by "reception month," here's how you can set it up:

1. Your Dimension will be MonthName(ReceptionDate) as you've stated.
2. Your expression for total weight by "expedition month" with display by "reception month" is a bit complex and you need to adjust your Set Analysis.

To achieve the desired result, you might consider the following approach:

1. Dimension:

Monthname(ReceptionDate)

1. Expression:

Sum({$<ExpeditionDate = {"=$(=MonthStart(ReceptionDate))"}>} Weight)

Here's a breakdown:

• Monthname(ReceptionDate) will give you the month-year representation of the ReceptionDate.

• The Set Analysis expression, {...}, allows you to conditionally sum your data based on certain criteria:

  • $<...> makes sure your current selections in other fields are still considered.

  • ExpeditionDate = {"=$(=MonthStart(ReceptionDate))"} restricts the data set to records where the ExpeditionDate falls within the month of the ReceptionDate in the current row of your table.

With this setup, for each row in your table (which represents a ReceptionDate month-year), the measure will give you the sum of Weight for records where the ExpeditionDate falls within that same month-year.

[Lou_Lab]

Hi @Mike_Dickson are you sure about this ? because it return nothing for me 😕 

Thanks !