cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

Last Date of fiscal months

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"}}
})

5 REPLIES 5
Community Support

Hi @GigaFluxx ,

Is this problem solved?

Best Regards,

Icey

Super User

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]
)
``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...