Forum Discussion

leodec11's avatar
leodec11
Frequent Visitor
11 months ago

Hi

the sample data in table, and added reult expected in picture

SectionClassRoomYearMnthDENONUMER
class APclass A202506223
class APclass A202506110
class APclass A202506534
class APclass A20250618
class APclass A202506428
class APclass A2025061034
class APclass A202506228
class APclass A202506330
class APclass A202506856
class APclass A20250611
class APclass A202506148
class Rclass A202506533
class Rclass A202506976
class Rclass A202507871
class NMclass A202506217
class NMclass A202506110
class NMclass A202507227
class NMclass A202507536
class HEclass A202506550
class HEclass A20250617
class HEclass A202506330
class HEclass A202506329
class HEclass A202507110
class Nclass A202506227
class Nclass A202506110
class Nclass A20250626
class Nclass A202506110
class Nclass A202506227

 . 

I have built below column

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 ) )
    )
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper 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 ) )
    )