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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

Top value

Dear Friend,

i have two measures: Hours and Day as output data shown below, i want to get Top 1 Hours and Day based on Hours. result for below data: Top1:  56,758 hours and 23 Day

Another example: if i will have data like below, if Day is empty then then Hours=Hours+last above hours

and Day=Day+above Day.

here Day is empty in April so Hours=41000+23371=64371 and Day=23+30, the result table in on right side. then we need to find Top1 based on hours i.e. hours=64371 and Day=53

Another example:

then Top1 is Hours=13419 and Day 58

how can i get using Dax ?

1 ACCEPTED SOLUTION
Community Support

Below is an example I created to achieve the effect you want.

My sample:

1. I created two measures to calculate the hours and days per month

``Hours = CALCULATE(SUM('Table'[Hour]), ALLEXCEPT('Table', 'Table'[Month]))``

``Days = CALCULATE(SUM('Table'[Day]), ALLEXCEPT('Table', 'Table'[Month]))``

2. Create several calculated columns as follow

``rank = RANKX('Table', [Month], , ASC, Dense)``

``````Hours1 =
VAR _lRank = [rank] - 1
VAR _lhours = MAXX(FILTER('Table', [rank] = _lRank), [Hours])
RETURN
IF([Days] = BLANK(), [Hours] + _lhours, [Hours])``````

``````Days1 =
VAR _monthDays = DAY(EOMONTH([Month], 0))
VAR _lRank = [rank] - 1
VAR _lDays = MAXX(FILTER('Table', [rank] = _lRank), [Days])
RETURN
IF([Days] = BLANK(), _lDays + _monthDays, [Days])``````

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Super User

Hi,

Share the download link of the PBI file.

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

Below is an example I created to achieve the effect you want.

My sample:

1. I created two measures to calculate the hours and days per month

``Hours = CALCULATE(SUM('Table'[Hour]), ALLEXCEPT('Table', 'Table'[Month]))``

``Days = CALCULATE(SUM('Table'[Day]), ALLEXCEPT('Table', 'Table'[Month]))``

2. Create several calculated columns as follow

``rank = RANKX('Table', [Month], , ASC, Dense)``

``````Hours1 =
VAR _lRank = [rank] - 1
VAR _lhours = MAXX(FILTER('Table', [rank] = _lRank), [Hours])
RETURN
IF([Days] = BLANK(), [Hours] + _lhours, [Hours])``````

``````Days1 =
VAR _monthDays = DAY(EOMONTH([Month], 0))
VAR _lRank = [rank] - 1
VAR _lDays = MAXX(FILTER('Table', [rank] = _lRank), [Days])
RETURN
IF([Days] = BLANK(), _lDays + _monthDays, [Days])``````

Result:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Responsive Resident

hi
try below dax measures

1.AdjustedHoursAndDay = VAR CurrentHours = 'YourTable'[Hours] VAR CurrentDay = 'YourTable'[Day] VAR PreviousHours = CALCULATE(MAX('YourTable'[Hours]), FILTER('YourTable', 'YourTable'[Day] = EARLIER('YourTable'[Day]) - 1)) VAR PreviousDay = CALCULATE(MAX('YourTable'[Day]), FILTER('YourTable', 'YourTable'[Day] = EARLIER('YourTable'[Day]) - 1)) RETURN IF(ISBLANK(CurrentDay), CurrentHours + PreviousHours, CurrentHours)

2.

```Top1HoursAndDay =
VAR TopHours = CALCULATE(MAX('YourTable'[AdjustedHoursAndDay]))
VAR TopDay = CALCULATE(MAX('YourTable'[Day]), FILTER('YourTable', 'YourTable'[AdjustedHoursAndDay] = TopHours))
RETURN
CONCATENATE(TopHours, " hours and ", TopDay, " day")```

@Jyaulhaq

Frequent Visitor

i have Hours and Day measures, not column. Please do needful

Helpful resources

Announcements

New forum boards available in Real-Time Intelligence.

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

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors