calculate with diffrent types

[netanel]

Hi All,
I need your help in this case,

I have this measure that combines different values with two different types percentage and whole number.

Combined_Measure =

    SWITCH (

        TRUE (),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "TTD Applications", FORMAT ( [total_submitted (Fact_Applications)], "#,##0" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Prequal Approved", FORMAT ( [# Of Applications], "#,##0" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Prequal Declined", FORMAT ( [# Of Applications], "#,##0" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Prequal Approval Rate", FORMAT ( [% Soft Approved/Submitted], "0.00%" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Full App Submit", FORMAT ( [# Of Applications], "#,##0" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Full App Approved", FORMAT ( [# Of Applications], "#,##0" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Full App Declined", FORMAT ( [# Of Applications], "#,##0" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Full App Approval Rate", FORMAT ( [% Soft hardApproved/SoftApproved], "0.00%" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Overall Approval Rate", FORMAT ( [% Approved (Fact Applications)], "0.00%" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Booked Applications", FORMAT (  [# Of Applications], "#,##0" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Booked Application Volume", FORMAT ( SUM(Fact_Applications[LineOfCredit_USD]), "$#,##" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Pull Through Rate", FORMAT ( [% Soft Accpted/HardpullApproved] ,  "0.00%" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Monetized LOCs", FORMAT ( SUM('Fact_Applications'[Count_Purchases_KPI]), "#,##" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Monetization Rate", FORMAT ( [% Monetization Rate] ,  "0.00%" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "monetized Volume", FORMAT ( SUM('Fact_Transactions'[Total_Funded_USD]), "$#,##" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Average Approved Line Amount", FORMAT ([Avg LOC For KPI], "$#,##" ),

        SELECTEDVALUE ( 'KPI'[Sub Title] ) = "Average Order Value", FORMAT ([Avg_Total_Funded_USD], "$#,##" ),

        BLANK ()

    )
to show this in matrix table and get last six month from the user selction
I use this measure

Combined Measures | Last 6 Months =

Var MaxDate = MAX(Dim_Date[ISODateName])

Var MaxDate_6MonthAgo = EOMONTH(MaxDate,-6)

Var Result = if( HASONEVALUE( 'Last 6 Month Date'[MonthAbbrev]) && HASONEVALUE('Last 6 Month Date'[Year]) &&

                MAX( 'Last 6 Month Date'[ISODateName]) <= MaxDate &&

                MIN( 'Last 6 Month Date'[ISODateName]) >  MaxDate_6MonthAgo ,

                CALCULATE([Combined_Measure],

                FILTER( ALL(Dim_Date[MonthAbbrev],Dim_Date[Year]),

                Dim_Date[MonthAbbrev] = VALUES( 'Last 6 Month Date'[MonthAbbrev]) &&

                Dim_Date[Year] = VALUES('Last 6 Month Date'[Year]))),BLANK())

RETURN

      Result

so if the user selects March 2024 
he gets in the table from Oct 2023 to Mar 2024
my problem is this
1. I can't use Column subtotal in my table (Matrix table) I don't see any value 
So I use another measure for the total period
This is the measure

Combined Measures | Sum Last 6 Months =

VAR MaxDate = MAX(Dim_Date[ISODateName])

VAR MaxDate_6MonthAgo = EOMONTH(MaxDate,-6)

VAR Result =

    IF(

        HASONEVALUE('Last 6 Month Date'[MonthAbbrev]) && HASONEVALUE('Last 6 Month Date'[Year]) &&

        MAX('Last 6 Month Date'[ISODateName]) <= MaxDate &&

        MIN('Last 6 Month Date'[ISODateName]) > MaxDate_6MonthAgo,

        CALCULATE(

            [Combined_Measure],

            DATESBETWEEN(

                Dim_Date[ISODateName],

                MaxDate_6MonthAgo + 1,

                MaxDate

            )

        ),

        BLANK()

    )

RETURN

    Result
that brings me to the second problem

2. if I want to add a column that shows the change between the periods
let's say the user selects Mar 2024
and get from Oct 2023 to Mar 2024
the Prior Year % the change should be
Oct 2023 to Mar 2024 / Oct 2022 to Mar 2023
I tried this measure and get not correct amount

Prior Year % =

VAR CurrentPeriodStart = MIN(Dim_Date[ISODateName])

VAR CurrentPeriodEnd = MAX(Dim_Date[ISODateName])

VAR PriorYearStart = DATE(YEAR(CurrentPeriodStart) - 1, MONTH(CurrentPeriodStart), DAY(CurrentPeriodStart))

VAR PriorYearEnd = DATE(YEAR(CurrentPeriodEnd) - 1, MONTH(CurrentPeriodEnd), DAY(CurrentPeriodEnd))

VAR CurrentPeriodSum = CALCULATE ( [Combined_Measure], DATESBETWEEN(Dim_Date[ISODateName], CurrentPeriodStart, CurrentPeriodEnd))

VAR PriorYearSum = CALCULATE ( [Combined_Measure], DATESBETWEEN(Dim_Date[ISODateName], PriorYearStart, PriorYearEnd))

VAR CurrentPeriodNumeric = VALUE(SUBSTITUTE(SUBSTITUTE(CurrentPeriodSum, "%", ""), ",", ""))

VAR PriorYearNumeric = VALUE(SUBSTITUTE(SUBSTITUTE(PriorYearSum, "%", ""), ",", ""))

VAR PercentageChange = DIVIDE(CurrentPeriodNumeric, PriorYearNumeric) - 1

RETURN

    IF (

        ISBLANK(CurrentPeriodNumeric) || ISBLANK(PriorYearNumeric),

        BLANK(),

        FORMAT(PercentageChange, "0.00%")

    )

I will be glad for help in this case
Note
for this case, I created a new date table called 'Last 6 Month Date'
she did not connect to my dim date
Based on this YouTube advice
https://www.youtube.com/watch?v=wLPvYq82TN4

[v-kongfanf-msft]

Hi @netanel ,

Could you provide some screenshot information of the data model and describe it, it would be great to have relevant test data.

Best Regards,
Adamk Kong

[netanel · Accepted solution]

Hi thanks but
I found the solution 
I just wrapped the measure separately with the following adjustments:
Prior Year % =

VAR SumLast6Months = [Combined Measures | Sum Last 6 Months]

VAR SumLast6MonthsLastYear = [Combined Measures | Sum Last Year]

VAR NoDataAvailable = ISBLANK(SumLast6Months) || SumLast6Months = "" || ISBLANK(SumLast6MonthsLastYear) || SumLast6MonthsLastYear = ""

VAR SumLast6MonthsNumeric = IF(RIGHT(SumLast6Months, 1) = "%", VALUE(LEFT(SumLast6Months, LEN(SumLast6Months) - 1)) / 100, SumLast6Months)

VAR SumLast6MonthsLastYearNumeric = IF(RIGHT(SumLast6MonthsLastYear, 1) = "%", VALUE(LEFT(SumLast6MonthsLastYear, LEN(SumLast6MonthsLastYear) - 1)) / 100, SumLast6MonthsLastYear)

VAR PercentageChange = DIVIDE(SumLast6MonthsNumeric, SumLast6MonthsLastYearNumeric) - 1

RETURN

    IF (

        NoDataAvailable,

        BLANK(),  

        FORMAT(PercentageChange, "0.00%")  

    )