Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

value multiply by succeeding month count

Hi all,

 

This is the given table.


Is the below output possible in DAX?


Thanks in advance.

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    5 years ago

    Hi, Anonymous ,

     

    According to your description, you could create table to output 12 months and use GENERATE() to connect 2 tables,
    then create a column of calculated values, and finally put it in matrix and sort by index. the following formula to create :

    Step1: Enter data to create a table and add index column:

    Step2: New table 

    Gnew_table = GENERATE(SUMMARIZE('Table','Table'[Items]),'Table 2')

    Step3: Create a column

    value =
VAR a =
RELATED ( 'Table'[Amount] )
* (
MONTH ( CONVERT ( LEFT ( 'Gnew_table'[date], 3 ) & " 1", DATETIME ) )
- MONTH (
CONVERT ( LEFT ( RELATED ( 'Table'[Start of Saving] ), 3 ) & " 1", DATETIME )
) + 1
)
RETURN
IF ( a > 0, a, BLANK () )

    Step4: Sort by Index column

    Last create a matrix ,The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try a measure like

     


    measure =
    var _min = minx(allselected('Date'),'Date'[Date])
    var _val = minx(allselected('Table'),'Table'[value])
    return
    _val1 * datediff(_min, max('Date'[Date], month))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      thanks for quick response. 

      i updated my problem.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous ,

     

    According to your description, you could create table to output 12 months and use GENERATE() to connect 2 tables,
    then create a column of calculated values, and finally put it in matrix and sort by index. the following formula to create :

    Step1: Enter data to create a table and add index column:

    Step2: New table 

    Gnew_table = GENERATE(SUMMARIZE('Table','Table'[Items]),'Table 2')

    Step3: Create a column

    value =
VAR a =
RELATED ( 'Table'[Amount] )
* (
MONTH ( CONVERT ( LEFT ( 'Gnew_table'[date], 3 ) & " 1", DATETIME ) )
- MONTH (
CONVERT ( LEFT ( RELATED ( 'Table'[Start of Saving] ), 3 ) & " 1", DATETIME )
) + 1
)
RETURN
IF ( a > 0, a, BLANK () )

    Step4: Sort by Index column

    Last create a matrix ,The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    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
      5 years ago

      Thanks! this resolved my problem. You're a life saver 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello!

       

      is it possible to apply the frequency by quarterly or biannually?

  • MikeJohnsonZA's avatar
    MikeJohnsonZA
    Icon for Responsive Resident rankResponsive Resident
    5 years ago

    Hi Anonymous 

    there are a few things going on in this calculation, the first is to deal with these items one at a time, even for the total, for this, I have used the SUMX function, then we need to calculate the date difference between the [Start of savings date] and the current [year month](or period). Combining these two we get the following calc.

     

    Measure1 =
    SUMX (
        CALCULATETABLE (
            VALUES ( 'items'[Item] ),
            ALL ( Dates )
        ),
        VAR _EndOFDateRange =
            MAX ( 'Dates'[Date] )
        VAR _StartDate =
            CALCULATE (
                MIN ( 'items'[Start of Savings] ),
                ALL ( 'Dates' )
            )
        VAR _NumberOfMonths =
            DATEDIFF (
                _StartDate,
                _EndOFDateRange,
                MONTH
            ) + 1
        VAR _Amount =
            CALCULATE (
                SUM ( 'items'[Amount] ),
                ALL ( Dates )
            )
        RETURN
            IF (
                _NumberOfMonths > 0,
                _NumberOfMonths * _Amount,
                BLANK ()
            )
    )

     

    The image below shows that this aligns with what you have, however, there are a few limitations you would only ever be able to have a single amount and date for a given item.