Forum Discussion

Kavelar1's avatar
Kavelar1
Frequent Visitor
1 year ago
Solved

T12 Flex-Flow Calculation

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])


Need Help

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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's avatar
      Kavelar1
      Frequent Visitor
      1 year ago

      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. 

       

       

      MonthRevenueGOP
      1/1/202395,79525,684
      2/1/202380,86030,215
      3/1/2023134,88644,039
      4/1/202386,26538,529
      5/1/2023117,19440,238
      6/1/2023124,13152,381
      7/1/2023140,26354,150
      8/1/202396,02332,657
      9/1/2023121,09030,593
      10/1/2023147,22164,547
      11/1/2023144,82052,520
      12/1/202380,76926,418
      1/1/2024139,73535,379
      2/1/2024142,95542,340
      3/1/2024144,92543,217
      4/1/2024147,96957,212
      5/1/202485,31131,735
      6/1/2024133,70755,707
      7/1/2024108,69330,941
      8/1/2024105,65834,678
      9/1/202498,43128,195
      10/1/202482,74733,187
      11/1/2024139,15046,619
      12/1/2024145,72542,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!
  • v-venuppu's avatar
    v-venuppu
    Community Support
    1 year ago

    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.

  • v-venuppu's avatar
    v-venuppu
    Community Support
    1 year ago

    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's avatar
    v-venuppu
    Community Support
    1 year ago

    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.