Forum Discussion

DirkVr's avatar
DirkVr
Frequent Visitor
2 years ago

Latest month in a multi language report

I have an embedded multi language report where the top of the report looks like this in English

In Dutch it looks like this

The first slicer linked to the second slicer via field parameters.

TranslatedPeriodSelection = {
    ("By year", NAMEOF('PublishingCalendar2'[Year]), 0, "en-GB",1),
    ("By month", NAMEOF('PublishingCalendar2'[MonthYearEN]), 1, "en-GB",2),
    ("By week", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "en-GB",3),
    ("Per jaar", NAMEOF('PublishingCalendar2'[Year]), 0, "nl",1),
    ("Per maand", NAMEOF('PublishingCalendar2'[MonthYearEN]), 1, "nl",2),
    ("Per week", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "nl",3),
    ("Par année", NAMEOF('PublishingCalendar2'[Year]), 0, "fr",1),
    ("Par mois", NAMEOF('PublishingCalendar2'[MonthYearEN]), 1, "fr",2),
    ("Par semaine", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "fr",3),
    ("Nach Jahr", NAMEOF('PublishingCalendar2'[Year]), 0, "de",1),
    ("Nach Monat", NAMEOF('PublishingCalendar2'[MonthYearEN]), 1, "de",2),
    ("Nach Woche", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "de",3)
}

The issues lies in the dropdown "By Month". The value for the last month is always "Latest" (while in dutch this should be "laatste")

In order to make the "By month", I have modified the calendar table so that dates of the latest month, will get as value "Latest".

I have used the following M-query formula : 

= Table.AddColumn(Source2, "ConvertedMonthYear", each if [converted_date] >= StartOfLastMonth and [converted_date] <= EndOfLastMonth then "Latest" else [MonthYear])

You see here that i filled in hardcoded "Latest".

 

I have tried adding 2 columns in the calendar : one for English (where the value "Latest" is used) and one for Dutch (where the value value "Laatste" is used). My field parameter is also modified :

TranslatedPeriodSelection = {
    ("By year", NAMEOF('PublishingCalendar2'[Year]), 0, "en-GB",1),
    ("By month", NAMEOF('PublishingCalendar2'[MonthYearEN]), 1, "en-GB",2),
    ("By week", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "en-GB",3),
    ("Per jaar", NAMEOF('PublishingCalendar2'[Year]), 0, "nl",1),
    ("Per maand", NAMEOF('PublishingCalendar2'[MonthYearNL]), 1, "nl",2),
    ("Per week", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "nl",3),
    ("Par année", NAMEOF('PublishingCalendar2'[Year]), 0, "fr",1),
    ("Par mois", NAMEOF('PublishingCalendar2'[MonthYearEN]), 1, "fr",2),
    ("Par semaine", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "fr",3),
    ("Nach Jahr", NAMEOF('PublishingCalendar2'[Year]), 0, "de",1),
    ("Nach Monat", NAMEOF('PublishingCalendar2'[MonthYearEN]), 1, "de",2),
    ("Nach Woche", NAMEOF('PublishingCalendar2'[WeekYear]), 2, "de",3)
}

But then I ran into an issue with the initial load. The Power BI report is stored in English language with the value "Latest" selected for the dropdown "Month".  When I want to load the report in Dutch (by passing language parameter = "nl"), the initial value of the month dropdown is "Latest" resulting in the following view

 

 

Any ideas how I can solve this issue?

Need Help

1 Reply