Total Revenue YTD for ALL years

[jwasilko]

I have a report that highlights different metrics for sales. I created a DAX measure to calculate the [Total Revenue] but I have revenue data from 2020-2024. 2024 is a partial year, meaning that my data only goes until the latest full month, June. This will obviously change as the year goes on, so I need my measure to be dynamic. 

I need a DAX measure that will calculate the total revenue, but I want it to be comparable and reflect the current months I have for 2024. Meaning I want a sum of revenue for 2020, 2021, 2022, etc. but only for the months that I have for 2024 (until June, 6 months). 

I want to make sure the measure is dyamic enough that I don't have to go and change the dax measure everytime I update the data within the report. I've tried different measures to achieve this but I cannot find a solution. (I've also just applied filters to my page to exclude certain months, but again not dynamic for updates). Is there a way to do this? 

[jwasilko]

Total Revenue = SUM('2020-2024'[Revenue])

Total Revenue YTD = 
VAR _LatestMonth = [Latest Month]
RETURN 
CALCULATE([Total Revenue], DATESYTD('Calendar'[Date]),
FILTER('Calendar', 'Calendar'[Month Num] <= _LatestMonth))

Latest Month = 
VAR _LatestDate = 
CALCULATE(
    MAX('Calendar'[Date]),
    ALL('Calendar')
)
VAR _LatestMonth = MONTH("06/30")
RETURN 
_LatestMonth

And also a screenshot of my measures in a card visual with no slicer selected: 

Top left visual is my [Total Revenue], which sums the total revenue of all 4 years. The top right is my [Total Revenue YTD], but with no slicer selected automatically shows the revenue for 2024 (which is already "year to date" since it is a partial year). And the bottom visual is my [Total Revenue] measure but with filters on it to exclude the months that are not present in my dataset for 2024 (Jan-Jun). So I'm looking for a DAX measure that will essentially accomplish what the bottom visual is doing, but without having to use filters and manually change the measure/report when updates occur. Calculating the total revenue of 2020, 2021, 2022, etc. but only in the months that are available for 2024, so that the numbers are comparable. 

With 2023 selected on my slicer this is the results I am getting. whichis close to what I want but still needs work on the back end of things. Is there a DAX measure that can help?  

[v-linyulu-msft]

Thanks for the reply from @Greg_Deckler  please allow me to provide another insight:

Hi, @jwasilko 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Then I created a calculated table like this as a slicer, and it remains unrelated to the original table:

date11 = VALUES('2020-2024'[Date])

3.To calculate the sum of four years of earnings, I created the following measure:

Total Revenue = SUM('2020-2024'[Revenue])

4.For calculating the YTD for the selected year, I created the following measure:

Total Revenue YTD =
VAR select1 =
    MAX ( 'date11'[Date] )
RETURN
    CALCULATE (
        SUM ( '2020-2024'[Revenue] ),
        FILTER ( '2020-2024', '2020-2024'[Date].[Year] = YEAR ( select1 ) )
    )

5.To calculate the sum of the months of existence for the selected year and 2024, I've created the following measure:

Compare Revenue =
IF (
    ISFILTERED ( date11[Date].[Year] ),
    CALCULATE (
        SUM ( '2020-2024'[Revenue] ),
        FILTER (
            '2020-2024',
            '2020-2024'[Date].[Year] = YEAR ( MAX ( 'date11'[Date] ) )
                && '2020-2024'[Date]
                    <= EDATE (
                        MAX ( '2020-2024'[Date] ),
                        -12 * DATEDIFF ( MAX ( 'date11'[Date] ), MAX ( '2020-2024'[Date] ), YEAR )
                    )
        )
    ),
    CALCULATE ( SUM ( '2020-2024'[Revenue] ), DATESYTD ( '2020-2024'[Date] ) )
)

6.Here's my final result, which I hope meets your requirements.
 

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[jwasilko]

I'm not sure I understand why it is necessary to create the calculated table 'date11'. I followed as you said though and the results are still not what I am looking for. the The [Total Revenue YTD] measure you shared with me, is giving me blanks with and without slicer selection. And the [Compare Revenue] measure is still giving me the sum for the full year when I select one in a slicer. It is also automatically giving me the revenue for 2024 with no slicer selected, when I want it to sum up the total revenue of all 4 years but only with the releveant dates in 2024 (currently is Jan-Jun but will change over time as the year completes, I want my DAX measure to be dynamic to handle these changes/updates) when no slicer is selected. Then once a year is selected, say 2023, it gives me the total revenue for 2023 but for the matching months available in 2024, again Jan-Jun. 

Here's what the measures you suggested look like in my report. 

Total Revenue YTD 1 = 
VAR select1 =
    MAX ( 'Date'[Date])
RETURN
    CALCULATE (
        SUM ( '2020-2024'[Revenue] ),
        FILTER ( '2020-2024', '2020-2024'[Created On]  = YEAR ( select1 ) )


Compare Revenue = 
IF (
    ISFILTERED ( 'Date 2'[Created On].[Year]),
    CALCULATE (
        SUM ( '2020-2024'[Revenue] ),
        FILTER (
            '2020-2024',
            '2020-2024'[Created On] = YEAR ( MAX ( 'Date 2'[Created On]) )
                && '2020-2024'[Created On]
                    <= EDATE (
                        MAX ( '2020-2024'[Created On] ),
                        -12 * DATEDIFF ( MAX ( 'Date 2'[Created On] ), MAX ( '2020-2024'[Created On] ), YEAR )
                    )
        )
    ),
    CALCULATE ( SUM ( '2020-2024'[Revenue] ), DATESYTD ( '2020-2024'[Created On] ) )
)


Date = CALENDAR(DATE(2020, 1, 1), TODAY())
Date 2 (date11) = VALUES('2020-2024'[Created On])

[v-linyulu-msft · Accepted solution]

Hi,@jwasilko 

Regarding the issue you raised, my solution is as follows:
 

1.First of all, about your first question, the purpose of creating a calculated table date11 is to make a slicer, and there is no table relationship between the slicer and the original table. This way, the choice of slicer will not directly affect our original table, and thus will not cause the DAX output to be inconsistent with our desired results.

2.Secondly, regarding your second question, there is a gap in the Total Revenue YTD metric, I observed that the difference between my dax and what you provided is mainly in the filtering part, I used with [year], yours does not:

Here's a comparison of the two:
 

3.Finally, I've modified the false result of Compare Revenue according to your needs:

Compare Revenue =
IF (
    ISFILTERED ( date11[Date].[Year] ),
    CALCULATE (
        SUM ( '2020-2024'[Revenue] ),
        FILTER (
            '2020-2024',
            '2020-2024'[Date].[Year] = YEAR ( MAX ( 'date11'[Date] ) )
                && '2020-2024'[Date]
                    <= EDATE (
                        MAX ( '2020-2024'[Date] ),
                        -12 * DATEDIFF ( MAX ( 'date11'[Date] ), MAX ( '2020-2024'[Date] ), YEAR )
                    )
        )
    ),
    CALCULATE (
        SUM ( '2020-2024'[Revenue] ),
        FILTER (
            ALLSELECTED ( '2020-2024' ),
            MONTH ( '2020-2024'[Date] ) <= MONTH ( MAX ( '2020-2024'[Date] ) )
        )
    )
)

4.Here are my final results:

Please find the attached pbix relevant to the case.
 

Best Regards,

Leroy Lu

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

[jwasilko]

@Greg_Deckler Check inbox

[Greg_Deckler]

@jwasilko Sample data would help greatly. Maybe something like the following:

Measure = 
  VAR __CurrentMonth = MONTH( TODAY() )
  VAR __Result = CALCULATE( [Total Revenue], FILTER( ALL( 'Table' ), MONTH( [Date] ) <= __CurrentMonth )
RETURN
  __Result