Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Measures for half-year sales

Hi All

Can anyone help how to create measures for half-year sales like H1 and H2 for both Current year and Previous year please.

Thanks in Advance,

Sonia

  • FarhanJeelani's avatar
    FarhanJeelani
    1 year ago

    Hi Anonymous ,

     

    Define Half-Year Logic

    To separate H1 (January to June) and H2 (July to December), you can use the MONTH function.

    Current Year H1 and H2

    Create measures for H1 and H2 sales for the current year.

    H1 Sales Current Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) <= 6,
    YEAR(Sales[Date]) = YEAR(TODAY())
)

H2 Sales Current Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) > 6,
    YEAR(Sales[Date]) = YEAR(TODAY())
)

    Previous Year H1 and H2

    To calculate for the previous year, modify the YEAR filter accordingly:

    H1 Sales Previous Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) <= 6,
    YEAR(Sales[Date]) = YEAR(TODAY()) - 1
)

H2 Sales Previous Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) > 6,
    YEAR(Sales[Date]) = YEAR(TODAY()) - 1
)

    Optional Total Half-Year Sales

    If you'd like to create a combined measure for total half-year sales for both periods, you can sum up the respective measures:

    Total H1 Sales = [H1 Sales Current Year] + [H1 Sales Previous Year]
Total H2 Sales = [H2 Sales Current Year] + [H2 Sales Previous Year]

     

    Please mark this post as solution if it helps you. Appreciate Kudos.

     

4 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous ,

     

    Define Half-Year Logic

    To separate H1 (January to June) and H2 (July to December), you can use the MONTH function.

    Current Year H1 and H2

    Create measures for H1 and H2 sales for the current year.

    H1 Sales Current Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) <= 6,
    YEAR(Sales[Date]) = YEAR(TODAY())
)

H2 Sales Current Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) > 6,
    YEAR(Sales[Date]) = YEAR(TODAY())
)

    Previous Year H1 and H2

    To calculate for the previous year, modify the YEAR filter accordingly:

    H1 Sales Previous Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) <= 6,
    YEAR(Sales[Date]) = YEAR(TODAY()) - 1
)

H2 Sales Previous Year = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    MONTH(Sales[Date]) > 6,
    YEAR(Sales[Date]) = YEAR(TODAY()) - 1
)

    Optional Total Half-Year Sales

    If you'd like to create a combined measure for total half-year sales for both periods, you can sum up the respective measures:

    Total H1 Sales = [H1 Sales Current Year] + [H1 Sales Previous Year]
Total H2 Sales = [H2 Sales Current Year] + [H2 Sales Previous Year]

     

    Please mark this post as solution if it helps you. Appreciate Kudos.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Appreciate your help FarhanJeelani 

      I have another issue in creating a measure to calculate sales for the remaining period of the year.
      I am a beginner so please ignore my innonce when asking questions

      I have table Sales which has data from 2022-1-1 until 2024-12-29 and I have to calculate Year-to-Go (YTG) Sales, I dont have goal/target value for 2024 and I donno how should I assume it.

      • FarhanJeelani's avatar
        FarhanJeelani
        Icon for Super User rankSuper User
        1 year ago

        Hi Anonymous ,

        No worries at all! Everyone starts somewhere, and asking questions is the best way to learn. Let me help you with your "Year-to-Go" (YTG) Sales calculation.

        Since you don't have a specific goal or target value for 2024, you can calculate YTG Sales as the sum of sales for the remaining period of the current year based on today's date. Here's how you can do it using DAX:

        Measure for Year-to-Go (YTG) Sales:

        YTG Sales = 
CALCULATE(
    SUM(Sales[SalesAmount]),
    Sales[Date] >= TODAY(),
    YEAR(Sales[Date]) = YEAR(TODAY())
)

        Explanation:

        1. Sales[Date] >= TODAY(): This filters the rows to only include dates from today onward.
        2. YEAR(Sales[Date]) = YEAR(TODAY()): Ensures you only sum sales for the current year.

        If You Want to Extend This Logic:

        • For YTG Sales for future years, you can modify the logic to include dates beyond this year by adjusting the filter for the YEAR function.
        • If you eventually get target or goal data, you can compare the calculated YTG Sales against the target to track progress.

        Let me know if you need further clarification or adjustments! 😊

         

        Please mark this post as solution if it helps you. Appreciate Kudos.