Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

example1.png

5 REPLIES 5
Icey
Community Support
Community Support

Hi @GigaFluxx ,

 

Is this problem solved?

 

 

Best Regards,

Icey

Greg_Deckler
Super User
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]
  )
      

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.