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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ak77
Post Patron
Post Patron

Half year YTD

Hi All,

 

i want to calculate Half year YTD measure for a data set. Can anyone please help me out with the expression to calculate the same.

10 REPLIES 10
Ahmedx
Super User
Super User

It's better for you to create a calculated column in the calendar table like this
Half = IF('Calendar'[Month number]<=6, "Half-1",Half-2)
and then you just need to create a matrix where you have 'Calendar'[Half] on the columns and years on the rows, and then simply measure = SUM(table[Sales]

Screenshot_1.pngScreenshot_2.png

ThxAlot
Super User
Super User

Easy enough,

ThxAlot_0-1701246448241.png

 

For fun only, a showcase of powerful Excel formulas,

ThxAlot_0-1701248569946.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



@ThxAlot @this is awesome. Well done.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@ak77 try this measure, doesn't matter how you visualize the data, it will always do the running total of 1st half and then 2nd half, cheers:

 

 

RT Sales Half Yearly = 
VAR __SelectedDate = MAX ( 'Calendar'[Date] )
VAR __MonthOfSelectedDate = MONTH ( __SelectedDate )
VAR __YearOfSelectedDate = YEAR ( __SelectedDate )
VAR __StartDate = IF (__MonthOfSelectedDate < 7, DATE ( __YearOfSelectedDate, 1, 1 ), DATE ( __YearOfSelectedDate, 7, 1 ) ) 
VAR __EndDate = IF ( __MonthOfSelectedDate < 7, DATE ( __YearOfSelectedDate, 6, 30 ), DATE ( __YearOfSelectedDate, 12, 31 ) )
RETURN
CALCULATE ( 
    [Sales],
    FILTER ( 
        CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[Date] >= __StartDate, 'Calendar'[Date] <= __EndDate ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) 
    )
)

 

 

and here is good example of how it will work, you have sale by month and running total will reset 1st half and 2nd  half:

 

parry2k_1-1701232821878.png

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

thanks @parry2k , i wil check and get back to u

Ashish_Mathur
Super User
Super User

Hi,

If you select June in the month slicer and 2023 in the year slicer, then this measure will add the figures in the sales column from Jan - Jun

Measure = calculate(sum(Data[sales]),datesytd(calendar[date],"31/12"))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@ThxAlot @Ahmedx @Ashish_Mathur @gmsamborn , thanks guys.. wil get back after implementing one of the solutions posted

parry2k
Super User
Super User

@ak77 I guess you want 1st half and 2nd half - two measures, correct?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k , Thanks for reply.  i have a date and on selection of it For the measure should check if month number >6 then calculate sum for first 6 months , else last 6 months 

 

Ex: 28 Nov 2023, month is 11 so calculate last 6 months sum from july till date

 

Please let me know if this is possible

Would a column in the date table make things easier?

 

Semester = IF( [Quarter] <= 2, 1, 2 )

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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