Saturday, 21 June 2025

DAX Measure to calculate % of tickets closed within TAT

 %TICKETS_CLOSED_WITHIN_TAT(%Success) =

VAR FilteredTable =
    FILTER(
        'Closed,Rejected',
        'Closed,Rejected'[TAT_Closed_Req] = "Within TAT" //[TAT_Closed_Req] Is a measure
    )
RETURN
DIVIDE(
    CALCULATE(COUNT('Closed,Rejected'[Id]), FilteredTable),
    CALCULATE(COUNT('Closed,Rejected'[Id]))
)




Create Button Slicer for Monthly, Weekly & yearly Data

Video Ref:

https://www.youtube.com/watch?v=NA8KcFgxrG0


Step-1:
  • Modelling  > New Parameter > Fields
Step-2:
    • Select the parameters for Monthly,Weekly,Yearly as shown below
    Step-3:

        • After this, Insert button slicer and click on the parameter name which you created.


        Wednesday, 18 June 2025

        Ageing calculation using DAX

        • In the Below DAX function we are calculating ageing only for the tickets which are open.
        • If the Ticket is closed, then it is mentioned as "NA


        DAX Code

        Ageing Working Days =

        var status1=SELECTEDVALUE(Main[Status])
        RETURN
        if(
            status1 in {"Open"},
        CALCULATE(
            COUNTROWS('fct_Date Table'),
            DATESBETWEEN('fct_Date Table'[Date], max('Main'[SubmittedDate]), TODAY() - 1),
            'fct_Date Table'[IsWorkingDay] = TRUE,'Main'[Status] IN {"Open"},
            ALL('Main')),
            "NA"
        )

        Tuesday, 17 June 2025

        Creating Date Table in Power Bi

        Date_Table = ADDCOLUMNS(
        CALENDAR(DATE(2023,6,1)
        ,TODAY()),
        "Month-Year",FORMAT([Date],"mmm")&"-"&YEAR([Date]),
        "Month",FORMAT([Date],"mmm"),
        "MonthNum",FORMAT(MONTH([Date]),"00"),
        "Week-Year","Week-" &WEEKNUM([Date],2)&"-"&YEAR([Date]),
        "Year",YEAR([Date]),
        "YearMonth", YEAR([Date]) & FORMAT(MONTH([Date]), "00"),
        "IsWorkingDay",NOT WEEKDAY( [Date] ) IN { 1,7 }
        )

        Calculating Working Days in Power Bi DAX

        References :

        https://dax.guide/weekday/

         Step-1:

        Create a calculated column in Date table.

        "IsWorkingDay",NOT WEEKDAY( [Date] ) IN { 1,7 }


        Step-2: Workingdays to Close

        Working_Days_to_Close =
        CALCULATE(
            COUNTROWS('fct_Date Table'),
            DATESBETWEEN('fct_Date Table'[Date], max('RefTable'[SubmittedDate]), max('RefTable'[ClosedDate]) - 1),
            'fct_Date Table'[IsWorkingDay] = TRUE,
            ALL('RefTable')
        )


        Step-3: Days to Close

        Days to Close =
        var submitdate=max('RefTable'[SubmittedDate])
        var closeddate=max('RefTable'[ClosedDate])
        RETURN CALCULATE(DATEDIFF(submitdate,closeddate,DAY),ALL('fct_Date Table'))


        • ALL Function-> This function removes any filters that might be applied to the 'RefTable'. It ensures that the calculation considers all rows in the 'RefTable', regardless of any existing filters.

        Wednesday, 11 June 2025

        Power Bi Links

        Sample Project Links

         https://community.fabric.microsoft.com/t5/Translytical-Task-Flow-Gallery/bd-p/pbi_translyticalgallery


        Creating Progress Visual in real time

        Beautiful SVG Targets vs Actuals Indicators in Power BI (No Custom Visuals!)


        Download Animated Icons

        4000+ Free High-quality Animated Icons


        UI/UX Toolkit

        Microsoft Power BI

        DAX measure to get the Id record of last updated date

        Problem:
        I have a case where I need to display only the latest updated record for a Id.
        In the below image I need to display only the record which is updated on 11-06-2025 (Latest)




        Solution:

        DAX Measure

        IsLatestActivity = 
        VAR CurrentIQMS = MAX('POR D'[Id])
        VAR CurrentDate = MAX('POR D'[Updated Date])
        
        VAR LatestRow =
            TOPN(
                1,
                FILTER(
                    ALL('POR D'),
                    'POR D'[Id] = CurrentIQMS
                ),
                'POR D'[Updated Date], DESC
            )
        RETURN
        IF (
            COUNTROWS(
                FILTER(
                    LatestRow,
                    'POR D'[Id] = CurrentIQMS &&
                    'POR D'[Updated Date] = CurrentDate
                )
            ) > 0,
            1,
            0
        )

        Wednesday, 4 June 2025

        Convert 160 marks to 100

         To convert 160 marks to a scale of 100, you can use the following formula:

        Converted Marks=(Marks ObtainedTotal Marks)×100

        In this case, the marks obtained are 160 and the total marks are also 160. So,

        Converted Marks=(160160)×100=100

        Therefore, 160 marks out of 160 is equivalent to 100 marks out of 100