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
ak77
Helper V
Helper V

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.