Create Table Format For MIN Date First value and MAX Date Last Value

[Chetan007]

Data:

Result:

Case1:

Group	Min Date	Max Date
A	1220	1330
	350+400+100+150+220=1220	300+550+80+250+150=1330
	Date From 8-10 To 11-10

Case2:

Group	Min Date	Max Date
A	1260	1330
	340+450+100+150+220=1260	300+550+80+250+150=1330
	Date From 9-10 To 11-10

Case3:

Group	Min Date	Max Date
A	1260	1330
	340+450+100+150+220=1260	330+500+80+200+220=1330
	Date From 9-10 To 10-10

Case4:

Group	Min Date	Max Date
A	1330	1330
	330+500+80+200+220=1330	300+550+80+250+150=1330
	Date From 10-10 To 11-10

when I was select Date range then my output is Min Date first value sum and second is Max Date last value sum. I can't require any sum. And also Consider category wise sum not Group wise.

[grazitti_sapna]

Hi @Chetan007 ,

To create the desired table format with the Min Date and Max Date sums per category, you can follow these steps:

Approach

1. Group the data by the category.
2. For each category, identify the values corresponding to the Min Date and Max Date in the selected date range.
3. Calculate the sum of values for each category within the selected date range for Min Date and Max Date.
4. Display the results in the required format.

Create a Date Range Slicer:

• Add a slicer visual to your report and drag the 'Date' column into it. This will allow you to select the date range dynamically.

Create Calculated Columns or Measures:

1. Measure for Min Date Value Sum:
   • Create a measure to sum the values for the Min Date based on the selected range.       MinDateValueSum =
     VAR MinDate = MIN('Table'[Date])
     RETURN
     CALCULATE(
     SUM('Table'[Value]),
     'Table'[Date] = MinDate)

2. Measure for Max Date Value Sum:

   • Create another measure for the Max Date values:                                                    MaxDateValueSum =
     VAR MaxDate = MAX('Table'[Date])
     RETURN
     CALCULATE(
     SUM('Table'[Value]),
     'Table'[Date] = MaxDate)

Display Category-wise Sum:

• Place the 'Category' column in a table visual.
• Add the measures MinDateValueSum and MaxDateValueSum to the values section of the table visual.
• This will display the sum of values for each category based on the Min and Max dates in the selected range.

Apply Filters (if necessary):

• Use filters in Power BI to further refine your view based on the selected categories.

This approach will provide you with a dynamic table that updates based on the date range you select, showing the sum of values for each category at the minimum and maximum dates in that range.

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!

[bhanu_gautam]

@Chetan007 , Try using 

Load your data into Power BI. Ensure your data has columns for Group, Date, and Value.

Create a new table to store the Min Date and Max Date for each group.

MinMaxDates =
SUMMARIZE(
YourTable,
YourTable[Group],
"Min Date", MIN(YourTable[Date]),
"Max Date", MAX(YourTable[Date])
)

Create measures to calculate the sum of values for the Min Date and Max Date.

MinDateSum =
CALCULATE(
SUM(YourTable[Value]),
FILTER(
YourTable,
YourTable[Date] = MIN(MinMaxDates[Min Date])
)
)

Create a final table to display the results.

FinalTable =
SUMMARIZE(
MinMaxDates,
MinMaxDates[Group],
"Min Date Sum", [MinDateSum],
"Max Date Sum", [MaxDateSum],
"Date Range", CONCATENATE("Date From ", CONCATENATE(MIN(MinMaxDates[Min Date]), CONCATENATE(" To ", MAX(MinMaxDates[Max Date]))))
)

Visualize the Data
Use the FinalTable to create a table visualization in Power BI. Add the columns Group, Min Date Sum, Max Date Sum, and Date Range to the table.

This will give you a table with the Min Date first value sum and Max Date last value sum for each group, along with the date range.

[Chetan007]

when i use your suggestion the output is above.

but i can't require this solution.

My group is not fix i have multiple group. so i will give you multiple group example so please give me any solution.

Data:

Solution:

[Anonymous · Accepted solution]

Hi @Chetan007 

Please try this:

Here's the sample table:

Table:

Then add 2 measures:

MinDate =
VAR _currentGroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Mindate",
            CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Group] = EARLIER ( 'Table'[Group] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                )
            ),
        "_Maxdate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Group] = EARLIER ( 'Table'[Group] )
                )
            )
    )
VAR _vtable_min =
    SUMMARIZE (
        FILTER ( _vtable, 'Table'[Date] = [_Mindate] ),
        'Table'[Group],
        "_Values", SUM ( 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _vtable_min, [Group] = _currentGroup ), [_Values] )

MaxDate =
VAR _currentGroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Mindate",
            CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Group] = EARLIER ( 'Table'[Group] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                )
            ),
        "_Maxdate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Group] = EARLIER ( 'Table'[Group] )
                )
            )
    )
VAR _vtable_max =
    SUMMARIZE (
        FILTER ( _vtable, 'Table'[Date] = [_Maxdate] ),
        'Table'[Group],
        "_Values", SUM ( 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _vtable_max, [Group] = _currentGroup ), [_Values] )

The result is as follow:

Best Regards

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

[Chetan007]

Thank You so much for Your Suggestion