Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Rune_
Frequent Visitor

Dax formula that does not filter on calender table

I need to know how my DAX formula called "Fakturert" needs to be updated to filter on dimKalender. 

 

I have 3 tables 

Rune__0-1696322504743.png

Relationship between 
JobTask[Job No_] -->[JobLedgerEntry[Job No_]
dimKalender[Date] -->[JobLedgerEntry[Posting Date]

 

In Power Bi report view in my table where I use JobTask[Job No_] 

When I use dimKalender for filter it only filters correctly for one of my DAX formulas. 

 

Rune__1-1696322889781.png

Incorrectly for this Dax
Fakturert =
SUMX(
    FILTER(
        JobTask,
        JobTask[Job Task Type] = 4
    ),
    CALCULATE(
        SUM(JobLedgerEntry[Line Amount (LCY)]),
        FILTER(
            ALL(JobLedgerEntry),
            JobLedgerEntry[Job No_] = EARLIER(JobTask[Job No_]) &&
            JobLedgerEntry[Job Task No_] >= EARLIER(JobTask[Totaling.1]) &&
            JobLedgerEntry[Job Task No_] <= EARLIER(JobTask[Totaling.2]) &&
            JobLedgerEntry[Source Code] = "SALG"
        )
    )
)
 
Correct for this DAX
FakturertLinje =
VAR SelectedJobNo = SELECTEDVALUE(JobTask[Job No_])
VAR SelectedJobTaskNo = SELECTEDVALUE(JobTask[Job Task No_])

RETURN
CALCULATE(
        SUMX(
        FILTER(
            JobLedgerEntry,
            JobLedgerEntry[Job No_] = SelectedJobNo &&
            JobLedgerEntry[Job Task No_] = SelectedJobTaskNo &&
            JobLedgerEntry[Source Code] = "SALG"
        ),
        JobLedgerEntry[Line Amount (LCY)]
        )
    )
 Dataset
1 ACCEPTED SOLUTION
Rune_
Frequent Visitor

Solutions

Fakturert =
-SUMX(
    FILTER(
        JobTask,
        JobTask[Job Task Type] = 4
    ),
    CALCULATE(
        SUM(JobLedgerEntry[Line Amount (LCY)]),
        FILTER(SUMMARIZE(
            JobLedgerEntry,
            JobLedgerEntry[Job No_],
            JobLedgerEntry[Job Task No_],
            JobLedgerEntry[Posting Date],
            JobLedgerEntry[Source Code]
        ),
            JobLedgerEntry[Job No_] = EARLIER(JobTask[Job No_]) &&
            JobLedgerEntry[Job Task No_] >= EARLIER(JobTask[Totaling.1]) &&
            JobLedgerEntry[Job Task No_] <= EARLIER(JobTask[Totaling.2]) &&
            JobLedgerEntry[Source Code] = "SALG"
        )
    )
)

View solution in original post

1 REPLY 1
Rune_
Frequent Visitor

Solutions

Fakturert =
-SUMX(
    FILTER(
        JobTask,
        JobTask[Job Task Type] = 4
    ),
    CALCULATE(
        SUM(JobLedgerEntry[Line Amount (LCY)]),
        FILTER(SUMMARIZE(
            JobLedgerEntry,
            JobLedgerEntry[Job No_],
            JobLedgerEntry[Job Task No_],
            JobLedgerEntry[Posting Date],
            JobLedgerEntry[Source Code]
        ),
            JobLedgerEntry[Job No_] = EARLIER(JobTask[Job No_]) &&
            JobLedgerEntry[Job Task No_] >= EARLIER(JobTask[Totaling.1]) &&
            JobLedgerEntry[Job Task No_] <= EARLIER(JobTask[Totaling.2]) &&
            JobLedgerEntry[Source Code] = "SALG"
        )
    )
)

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.