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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Syndicate_Admin
Administrator
Administrator

Dax problem

Dear good afternoon I want to ask for your help to be able to perform the following measure or generacy to achieve the following

I have the following data

enterpriseDate iniEnd dateMonthly Value
A01/01/202231/12/20244
A01/01/202131/12/20233
A01/04/202030/06/20202
B01/01/202031/03/20206
B01/01/202131/03/20215

And I need to get this:

YEARSum value of the year depending on the number of months
202024
202151
202284
202384
202448

where you have to add depending on the year and number of months that represents in that year the monthly value

Thanks in advance

7 REPLIES 7
MFelix
Super User
Super User

Hi @manv23061997

 

Can you explain a little biut better what you want to achieve? Not understanding what means the  Sum value of the year depending on the number of months.

 

How do you get 24 for 2020 and 51 for 2021.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



If I tell you

Result 24 for 2020 is obtained as follows:

In the box I can see that there is a record of the company "A" that has as its start date: 01/04/2020 and final file: 30/06/2020 between that range of dates there are 3 months of difference, therefore 3 months multiplied by the monthly value 2 gives me 6 as a result in the year 2020, then there is also a record "B" that has as a start date: 01/01/2020 and end date: 31/03/2020 Between those dates there is 3 months of difference multiplied by the monthly value 6 results in 18.

So in the year 2020 I have as a total value 6 + 18 = 24

Result 51 for the year 2021 is obtained in the same way only considering the total value of the records that have included the year within the period of start date and end date.

Hi @mavn23061997 ,

 

I assume the years table is separated from the other table, create the following measure:

Values per Month = 
VAR temptable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                CROSSJOIN (
                    'Years_Table',
                    ADDCOLUMNS (
                        'DataValues',
                        "StartYear", YEAR ( 'DataValues'[Date ini] ),
                        "EndYear", YEAR ( 'DataValues'[End date] )
                    )
                ),
                "Flag",
                    IF ( [StartYear] <= [Years] && [EndYear] >= [Years], 1, 0 )
            ),
            [Flag] = 1
        ),
        "TotalMonths",
            SWITCH (
                TRUE (),
                [StartYear] = [Years],
                    DATEDIFF (
                        'DataValues'[Date ini],
                        MIN ( DATE ( [years], 12, 31 ), 'DataValues'[End date] ),
                        MONTH
                    ) + 1,
                [StartYear] > [Years], DATEDIFF ( DATE ( YEAR ( [StartYear] ), 12, 31 ) + 1, 'DataValues'[Date ini], MONTH ),
                [StartYear] < [Years]
                    && [EndYear] > [Years], DATEDIFF ( DATE ( [Years], 1, 1 ), DATE ( [Years], 12, 31 ), MONTH ) + 1,
                [EndYear] = [Years], DATEDIFF ( DATE ( [years], 1, 1 ), 'DataValues'[End date], MONTH ) + 1
            )
    )
RETURN
    SUMX ( tempTable, [TotalMonths] * 'DataValues'[Monthly Value] )

Now just use this measure with the years column:

MFelix_0-1679495345772.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I just tried it and it works, just out of curiosity if in case I had a calendar table related to start date and end date, how could the same result be achieved

This depends on how they are related.

 

What is the active relationship?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Start date

Try the following formula:

Values per Month = 
VAR temptable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                CROSSJOIN (
                    DISTINCT('Calendar'[Year]),
                    ADDCOLUMNS (
                        'DataValues',
                        "StartYear", YEAR ( 'DataValues'[Date ini] ),
                        "EndYear", YEAR ( 'DataValues'[End date] )
                    )
                ),
                "Flag",
                    IF ( [StartYear] <= [Year] && [EndYear] >= [Year], 1, 0 )
            ),
            [Flag] = 1
        ),
        "TotalMonths",
            SWITCH (
                TRUE (),
                [StartYear] = [Year],
                    DATEDIFF (
                        'DataValues'[Date ini],
                        MIN ( DATE ( [year], 12, 31 ), 'DataValues'[End date] ),
                        MONTH
                    ) + 1,
                [StartYear] > [Year], DATEDIFF ( DATE ( YEAR ( [StartYear] ), 12, 31 ) + 1, 'DataValues'[Date ini], MONTH ),
                [StartYear] < [Year]
                    && [EndYear] > [Year], DATEDIFF ( DATE ( [Year], 1, 1 ), DATE ( [Year], 12, 31 ), MONTH ) + 1,
                [EndYear] = [Year], DATEDIFF ( DATE ( [year], 1, 1 ), 'DataValues'[End date], MONTH ) + 1
            )
    )
RETURN
    SUMX ( tempTable, [TotalMonths] * 'DataValues'[Monthly Value] )

 

Don't forget to accept the correct answer to help others.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

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