Forum Discussion

Chetan007's avatar
Chetan007
Frequent Visitor
2 years ago
Solved

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

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 ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years 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
Icon for Super User rankSuper User
2 years 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!