Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
John_Dozen34999
Frequent Visitor

value multiply by succeeding month count

Hi all,

 

This is the given table.
Capture2.PNG

Is the below output possible in DAX?
Capture.PNG

Thanks in advance.

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @John_Dozen34999 ,

 

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:

v-yalanwu-msft_0-1619605185620.png

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

v-yalanwu-msft_1-1619605203591.png

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

v-yalanwu-msft_2-1619605380576.png

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.

View solution in original post

6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

Hi, @John_Dozen34999 ,

 

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:

v-yalanwu-msft_0-1619605185620.png

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

v-yalanwu-msft_1-1619605203591.png

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

v-yalanwu-msft_2-1619605380576.png

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.

Hello!

 

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

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

Hi @John_Dozen34999 

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. 

 

Capture.PNG

amitchandak
Super User
Super User

@John_Dozen34999 , 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))

thanks for quick response. 

i updated my problem.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.