Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Max month for max year

Hi everyone, 

 

I'm trying to calculate an inventory turnover with Power BI.

 

I want to divide those two columns : Volume that we have in stocks / volume that we sold (for that, it is ok, my measure is a simple divide)

And then I want to divide it by the last month of the last year 

I've tried this, but it doesn't work :

Max month = CALCULATE(
MAX('Company$Commision Entries'[Mois]),
FILTER('Company$Commision Entries',MAX('Company$Commision Entries'[Année nbre]))
)
It always says as a response "12", I have indeed 12 months for 2023 but I have only 5 for 2024, as it is related to may.
My months and years are in numbers. 
 
Does someone have an idea please ? 
 
Thanks 🙂
Need Help

3 Replies

  • ManuelBolz's avatar
    ManuelBolz
    Icon for Responsive Resident rankResponsive Resident
    2 years ago

    Hello Anonymous 

    If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

    If I understood you correctly, this should be your solution.

    VAR LastYear = MAX('Company$Commision Entries'[Année nbre])
VAR MaxMonth = 
    CALCULATE(
        MAX('Company$Commision Entries'[Mois]),
        FILTER(
            'Company$Commision Entries',
            'Company$Commision Entries'[Année nbre] = LastYear
        )
    )
RETURN
    DIVIDE(
        [Volume in Stock], 
        [Volume Sold] * MaxMonth
    )


    Best regards from Germany
    Manuel Bolz

    🟦Follow me on LinkedIn
    🟨How to Get Your Question Answered Quickly
    🟩Fabric Community Conference
    🟪My Solutions on Github

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you for your help !! it works 😄 

       

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    2 years ago
    InventoryTurnoverLastMonthLastYear = //Try this might help you
VAR LastYear = CALCULATE(
    MAX('Company$Commision Entries'[Année nbre]),
    ALL('Company$Commision Entries')
)
VAR MaxMonthLastYear = CALCULATE(
    MAX('Company$Commision Entries'[Mois]),
    FILTER(
        'Company$Commision Entries',
        'Company$Commision Entries'[Année nbre] = LastYear
    )
)
RETURN
DIVIDE(
    CALCULATE(
        SUM('Company$Commision Entries'[VolumeInStock]),
        FILTER(
            'Company$Commision Entries',
            'Company$Commision Entries'[Année nbre] = LastYear &&
            'Company$Commision Entries'[Mois] = MaxMonthLastYear
        )
    ),
    CALCULATE(
        SUM('Company$Commision Entries'[VolumeSold]),
        FILTER(
            'Company$Commision Entries',
            'Company$Commision Entries'[Année nbre] = LastYear &&
            'Company$Commision Entries'[Mois] = MaxMonthLastYear
        )
    ),
    0
)