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"

)

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.

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
)

Convert 160 marks to 100

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

$$\text{Converted Marks}=\left(\frac{\text{Marks Obtained}}{\text{Total Marks}}\right)\times 100$$

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

$$\text{Converted Marks}=\left(\frac{160}{160}\right)\times 100=100$$

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