Forum Discussion

aakif_aslam's avatar
aakif_aslam
Helper I
4 years ago
Solved

Matrix column difference on defined month category

Hello,

 

I have defined a month period starting from 15th to 14th of next month, currently starting from July 15 to August 14 and thereby on incremental basis.

I want to show Qty difference based on these defined months

 

I also have two other categories in hierarchy to be added in row.

Please Help. Thanks a lot!

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi aakif_aslam ,

     

    Here you add your month category in column field in matrix, if you add diff in value field as well, your result will expand. Here I suggest you to add month category in Row field in matrix or create a table visual.

    Firstly , create a date table.

    Date = 
VAR _BASIC =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
        "Year", YEAR ( [Date] ),
        "Flag",
            VAR _MonthName =
                FORMAT ( [Date], "MMM" )
            VAR _Nextmonth =
                EOMONTH ( [Date], +1 )
            VAR _NEXTNAME =
                FORMAT ( _Nextmonth, "MMM" )
            VAR _PREMONTH =
                EOMONTH ( [Date], -1 )
            VAR _PRENAME =
                FORMAT ( _PREMONTH, "MMM" )
            VAR _COMBINENPRE =
                COMBINEVALUES ( "-", _PRENAME, _MonthName )
            VAR _COMBINENEXT =
                COMBINEVALUES ( "-", _MonthName, _NEXTNAME )
            VAR _Day =
                DAY ( [Date] )
            RETURN
                IF ( _Day <= 14, _COMBINENPRE, _COMBINENEXT )
    )
VAR _ADDMINDATE =
    ADDCOLUMNS (
        _BASIC,
        "MINDate", MINX ( FILTER ( _BASIC, [Flag] = EARLIER ( [Flag] ) ), [Date] )
    )
VAR _ADDRANK =
    ADDCOLUMNS (
        _ADDMINDATE,
        "Rank", RANKX ( _ADDMINDATE, [MINDate],, ASC, DENSE )
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _ADDRANK, [Date], [Year], [Flag], [Rank] )
RETURN
    _SUMMARIZE

    Create a relationship between Date table and Data table by [Date] columns.

    Then create a measure to create the difference.

    Diff = 
VAR _CURSUM =
    SUM ( 'Table'[Value] )
VAR _PRESUM =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Rank] = MAX ( 'Date'[Rank] ) - 1 )
    )
RETURN
    _CURSUM - _PRESUM

    Result is as below.

    You can download my sample as below for more details.

     

    Best Regards,
    Rico Zhou

     

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

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Please provide sanitized sample data that fully covers your issue.

     

    Paste the data into a table in your post or use one of the file services. Please show the expected outcome.

    • speedramps's avatar
      speedramps
      Super User
      4 years ago

      Try this ...

       

      Click here to download PBIX 

       

      This example uses a Calendar with an original date key and adjusted year/month built using Power Query.

       

      you can relate this Calendar to your fact table using the original date, and then subtotal by the adjusted year/month.

       

      Using a Calendar to redefine dates for an organsiation's periods is consider a best practice because the calculation is done once for each day during the refresh and there are only 3650 days in 10 years.

       

      Whereas a DAX date calulations may have to calculate millions of fact rows rows for each query. 

       

      Happy New Year

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi aakif_aslam ,

         

        Do you want to calcualte the Qty difference during the adjacent two months? Do you have a date table in your data model? Could you share a sample file with us and show me a screenshot with the result you want?

         

        Best Regards,
        Rico Zhou

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi aakif_aslam ,

     

    Here you add your month category in column field in matrix, if you add diff in value field as well, your result will expand. Here I suggest you to add month category in Row field in matrix or create a table visual.

    Firstly , create a date table.

    Date = 
VAR _BASIC =
    ADDCOLUMNS (
        CALENDAR ( MIN ( 'Table'[Date] ), MAX ( 'Table'[Date] ) ),
        "Year", YEAR ( [Date] ),
        "Flag",
            VAR _MonthName =
                FORMAT ( [Date], "MMM" )
            VAR _Nextmonth =
                EOMONTH ( [Date], +1 )
            VAR _NEXTNAME =
                FORMAT ( _Nextmonth, "MMM" )
            VAR _PREMONTH =
                EOMONTH ( [Date], -1 )
            VAR _PRENAME =
                FORMAT ( _PREMONTH, "MMM" )
            VAR _COMBINENPRE =
                COMBINEVALUES ( "-", _PRENAME, _MonthName )
            VAR _COMBINENEXT =
                COMBINEVALUES ( "-", _MonthName, _NEXTNAME )
            VAR _Day =
                DAY ( [Date] )
            RETURN
                IF ( _Day <= 14, _COMBINENPRE, _COMBINENEXT )
    )
VAR _ADDMINDATE =
    ADDCOLUMNS (
        _BASIC,
        "MINDate", MINX ( FILTER ( _BASIC, [Flag] = EARLIER ( [Flag] ) ), [Date] )
    )
VAR _ADDRANK =
    ADDCOLUMNS (
        _ADDMINDATE,
        "Rank", RANKX ( _ADDMINDATE, [MINDate],, ASC, DENSE )
    )
VAR _SUMMARIZE =
    SUMMARIZE ( _ADDRANK, [Date], [Year], [Flag], [Rank] )
RETURN
    _SUMMARIZE

    Create a relationship between Date table and Data table by [Date] columns.

    Then create a measure to create the difference.

    Diff = 
VAR _CURSUM =
    SUM ( 'Table'[Value] )
VAR _PRESUM =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Rank] = MAX ( 'Date'[Rank] ) - 1 )
    )
RETURN
    _CURSUM - _PRESUM

    Result is as below.

    You can download my sample as below for more details.

     

    Best Regards,
    Rico Zhou

     

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