Forum Discussion

Chetan007's avatar
Chetan007
Frequent Visitor
1 year ago
Solved

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

Data:

Result:

Case1:

GroupMin DateMax Date
A12201330
 350+400+100+150+220=1220300+550+80+250+150=1330
 Date From 8-10 To 11-10 

 

Case2:

GroupMin DateMax Date
A12601330
 340+450+100+150+220=1260300+550+80+250+150=1330
 Date From 9-10 To 11-10 

 

Case3:

GroupMin DateMax Date
A12601330
 340+450+100+150+220=1260330+500+80+200+220=1330
 Date From 9-10 To 10-10 

 

Case4:

GroupMin DateMax Date
A13301330
 330+500+80+200+220=1330300+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.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      Chetan007
      Frequent Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    1 year ago

    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!