Forum Discussion

Ashish_Mathur's avatar
Ashish_Mathur
Super User
11 months ago

Hi,

Share the download link of the Excel file with your formulas written there so that i can understand how you arrived at those results.  I will convert those formulas into DAX measures.

leodec11's avatar
leodec11
Frequent Visitor
11 months ago

If possible can Jihwan_Kim super user look into this for me,  can't upload real dataset, so adding random numbers. Business requirement is to show previous year ratio which is numer/deno by section, classroom, for which i built baseratio column has calculation Numer/Deno, next 3 columns needs to have dynamic headers for last 3 months in MMM-YY format, for dynamic header built Last3months table. last column needs to be YTD. I built 3 matrixes same as we do in tableau and put aligned the all 3 matrixes side by side but in PBI it is showing space between matrix. As Super User suggested to use calculation group which erase the usage of individual matrix table. Sample data (RandomNumbers), FinalTableFormat Expected(RandomNumbers)

SectionPrevYrJun-25Jul-25Aug-25YTD
class R4.55.74.52.33.4
class AP3.46.93.42.54.6
class HE2.53.82.92.41.4
class NM5.82.51.82.52.3
class N7.523.93.92.5
SectionClassRoomYearMnthDENONUMER
class APclass A202506223
class APclass A202506148
class Rclass A202506533
class Rclass A202507871
class NMclass A202506217
class NMclass A202506110
class HEclass A202506550
class HEclass A20250617
class Nclass A202506110
class Nclass A20250626
Report Date =
DATE (
    DIVIDE ( 'test'[YearMnth], 100, 0 ),     -- Year
    MOD ( 'test'[YearMnth], 100 ),           -- Month
    1                                           -- First day
)
Measure :
BaseRatio =
DIVIDE (
    SUM ( 'test'[NUMER] ),
    SUM ( 'test'[DENO] )
)
Disconnected Table :- 
Last3Months =
VAR MaxMonth = EOMONTH(TODAY(), -1)  // last month
RETURN
ADDCOLUMNS(
    CALENDAR(EOMONTH(MaxMonth,-2)+1, MaxMonth),
    "MMM-YY", FORMAT([Date], "MMM-yy")
)
Calculation group :-
condition =
VAR _previousyear =
    MAXX (
        FILTER ( ALL ( 'calendar' ), 'calendar'[Date] = TODAY () ),
        'calendar'[Year]
    ) - 1
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'calendar'[Year] ) = _previousyear
            && NOT ISINSCOPE ( 'calendar'[Month-Year] ), CALCULATE ( SELECTEDMEASURE (), 'calendar'[Year] = _previousyear ),
        ISINSCOPE ( 'calendar'[Month-Year] ),
            VAR _t =
                FILTER (
                    ALL ( 'calendar'[Month-Year sort], 'calendar'[Month-Year] ),
                    'calendar'[Month-Year sort] <= TODAY ()
                )
            VAR _last3months =
                WINDOW ( 1, ABS, 3, ABS, _t, ORDERBY ( 'calendar'[Month-Year sort], DESC ) )
            RETURN
                CALCULATE ( SELECTEDMEASURE (), KEEPFILTERS ( _last3months ) )
    )