Last Date of fiscal months

[GigaFluxx]

I'm new to Power BI and learning as I go.

Anyway, I'm pulling from my company's cube and I want to have 2 queries.

1 the shows the last date for each fiscal month and 1 that shows the last date for the current fiscal month which would roll forward as time progressed.

I've pulled in Date, Fiscal Year, Fiscal Quarter, Fiscal Month, and Fiscal Week. I brough all those in because while my ask is for month, I want to be flexible in looking at other ranges.

I'm not really sure at this point where to go being so new. Previously I was using MDX in Power Pivot to get my desired results. Any help would be appreciated

= Cube.Transform(IMR3,
{
{Cube.AddAndExpandDimensionColumn, "[Date]", {"[Date].[Date].[Date]", "[Date].[Month Number].[Month Number]", "[Date].[Quarter Number].[Quarter Number]", "[Date].[Week Number].[Week Number]", "[Date].[Year].[Year]"}, {"Date.Date", "Date.Month Number", "Date.Quarter Number", "Date.Week Number", "Date.Year"}}
})

[Icey]

Hi @GigaFluxx ,

Is this problem solved?

Best Regards,

Icey

[Greg_Deckler]

Does it have to be in Power Query? Otherwise you could write DAX like this:

Max Date in Fiscal Month Column =
  MAXX(
    FILTER(
      'Table',
      [Date.Month.Number] = EARLIER([Date.Month.Number]) && 
        [Date.Year] = EARLIER([Date.Year])
    ),
    [Date.Date]
  )
      

[GigaFluxx]

I'm getting an error "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." and Year and Month Number are greyed out so I Assuem they're the error.

I converted Date to an actual date, but the year an dmonth number are originally text and I can convert to a whole number but it still doesn't remove the error.

Edit: I played around a bit more and now I'm getting "Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2." yet I have it laid out the same way

[Greg_Deckler]

Are you creating a measure or a column? Paste some sample data as text and I'll debug whatever is wrong.

[Greg_Deckler]

Not sure what the issue is, I pasted in the code and it worked like a champ. PBIX attached.