T12 Flex-Flow Calculation

[Kavelar1]

Hi again,

I am back with another issue. I am trying to display a T-12 based flex-flow calculation with a disconnected date on the x-axis for a bar chart. At the moment, when I apply my T12 filter what appears is, let's say for last month in which I want to see the comparison vs last year's same month, it only carries out the calculation for the prior year's months and plots it for such while omiting "the most recent" 12 months.  Below is my flex calculation and a Disconnected T12 filter measure from my calculation group that I am using on my other measures that correctly show T12-based calculations. As a heads up "'T12,2Y etc Purposes Dates'[Date]" is the table that is disconnected from the dates and is used on the other working measures for the T12,2Y etc filters. 

Any input would be very helpful.

Thanks!

Flex (-) % vs Prior Year =

VAR CurrentRevenue =

    CALCULATE(

        [Revenue],

        'Main Table'[Period] = "MTDCY"

    )

VAR PriorRevenue =

    CALCULATE(

        [Revenue],

        'Main Table'[Period] = "MTDCY",

        SAMEPERIODLASTYEAR('Date'[Date])

    )

VAR CurrentGOP =

    CALCULATE(

        [GOP],

        'Main Table'[Period] = "MTDCY"

    )

VAR PriorGOP =

    CALCULATE(

        [GOP],

        'Main Table'[Period] = "MTDCY",

        SAMEPERIODLASTYEAR('Date'[Date])

    )

VAR RevenueVariance = CurrentRevenue - PriorRevenue

VAR GOPVariance = CurrentGOP - PriorGOP

RETURN

IF(

    RevenueVariance >= 0,

    Blank(),

    (1 - DIVIDE(GOPVariance, RevenueVariance))

)

Disconnected T12 =

VAR _max = MAXX(ALLSELECTED('Date'), 'Date'[Date])

VAR _monthNumber = MONTH(_max)

RETURN

CALCULATE(

    Calculate(selectedmeasure(), DATESINPERIOD('Date'[Date],max('date'[date]),-12,month)),

    USERELATIONSHIP('T12,2Y etc Purposes Dates'[Date], 'Main Table'[Date])

[v-venuppu]

Hi @Kavelar1 ,

I hope the information provided is helpful.I wanted to check whether you were able to resolve the issue with the provided solutions.Please let us know if you need any further assistance.

Thank you.

[v-venuppu]

Hi @Kavelar1 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-venuppu]

Hi @Kavelar1 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Ashish_Mathur for the prompt response.

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[Ashish_Mathur]

Hi,

Your question is not clear.  Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Kavelar1]

Apologies. The top view is what it should look like but am using the main date table. The bottom view is what ends up appearing when placing the Disconnected T12 filter into the visual while plotting with the disconnected date table 'T12,2Y etc Purposes Dates.' The thing I am attempting to achieve is to allowing the user to select a year/month and see the flex-flow calculations for the last 12 months of the selected date as opposed to the most recent accesible data. 

Month	Revenue	GOP
1/1/2023	95,795	25,684
2/1/2023	80,860	30,215
3/1/2023	134,886	44,039
4/1/2023	86,265	38,529
5/1/2023	117,194	40,238
6/1/2023	124,131	52,381
7/1/2023	140,263	54,150
8/1/2023	96,023	32,657
9/1/2023	121,090	30,593
10/1/2023	147,221	64,547
11/1/2023	144,820	52,520
12/1/2023	80,769	26,418
1/1/2024	139,735	35,379
2/1/2024	142,955	42,340
3/1/2024	144,925	43,217
4/1/2024	147,969	57,212
5/1/2024	85,311	31,735
6/1/2024	133,707	55,707
7/1/2024	108,693	30,941
8/1/2024	105,658	34,678
9/1/2024	98,431	28,195
10/1/2024	82,747	33,187
11/1/2024	139,150	46,619
12/1/2024	145,725	42,491

Flow (+) % vs Prior Year =

VAR CurrentRevenue =

    CALCULATE(

        [Revenue],

        'Main Table'[Period] = "MTDCY"

    )

VAR PriorRevenue =

    CALCULATE(

        [Revenue],

        'Main Table'[Period] = "MTDCY",

        SAMEPERIODLASTYEAR('Date'[Date])

    )

VAR CurrentGOP =

    CALCULATE(

        [GOP],

        'Main Table'[Period] = "MTDCY"

    )

VAR PriorGOP =

    CALCULATE(

        [GOP],

        'Main Table'[Period] = "MTDCY",

        SAMEPERIODLASTYEAR('Date'[Date])

    )

VAR RevenueVariance = CurrentRevenue - PriorRevenue

VAR GOPVariance = CurrentGOP - PriorGOP

RETURN

IF(

    RevenueVariance <= 0,

    BLANK(),

    DIVIDE(GOPVariance, RevenueVariance)

)

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

See if my solution in the attached file helps.