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
jj1
Helper II
Helper II

calculate value between two date columns and syncing filter question

 

 

2 ACCEPTED SOLUTIONS
Jaywant-Thorat
Regular Visitor

Question 1: Measure for Date Comparison Between Two Columns

You want to return a value only if Date Created is in Jan 2024 and Date Mailed is not in Jan 2024.

Assume your table is named 'Sample', and the value to return is in the Measure column.

DAX Measure:
MailedAfterCreatedMonth =
CALCULATE (
     SUM ( Sample[Measure] ),
     FILTER (
                  Sample,
                  FORMAT ( Sample[Date Created], "YYYY-MM" ) = "2024-01" &&
                  FORMAT ( Sample[Date Mailed], "YYYY-MM" ) <> "2024-01"
       )

)

This will help you to Filters only rows where:

Date Created is in Jan 2024, and

Date Mailed is not in Jan 2024.

Returns the sum of Measure for those rows.

 

View solution in original post

DataNinja777
Super User
Super User

Hi @jj1 ,

 

For Item 1, where you need to return the value when Date Created is in January 2024 but Date Mailed is not in January 2024, the best approach is to work with a proper date table. First, create a date table covering the relevant range:

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

This date table can be used to build slicers and ensure consistent filtering across multiple tables. Although in this case, you're comparing two separate date columns in the same table, the measure itself can still use the logic with formatted values:

Measure_Jan2024_Created_NotMailed :=
CALCULATE(
    SUM(Sample[Measure]),
    FILTER(
        Sample,
        FORMAT(Sample[Date Created], "YYYY-MM") = "2024-01"
            && FORMAT(Sample[Date Mailed], "YYYY-MM") <> "2024-01"
    )
)

This measure will return the sum of Measure where the Date Created falls in January 2024 but the Date Mailed does not.

For Item 2, where selecting a month should return both the Sales value from Table1 and the Refund value from Table2 for that month, it is better to use a shared DateTable rather than a disconnected month list. After creating the DateTable, set up relationships from DateTable[Date] to both Table1[Date] and Table2[Date].

Then, instead of using TREATAS, you can simply create measures that leverage the filter context provided by slicers based on the DateTable:

Sales_Selected :=
CALCULATE(
    SUM(Table1[Sales])
)
Refund_Selected :=
CALCULATE(
    SUM(Table2[Refund])
)

These measures will both respond to any filters applied via the DateTable, such as a slicer using DateTable[Year-Month]. This approach is more scalable and avoids issues with inconsistent sorting or ambiguous text-based month matching, making it much more suitable for professional, production-grade Power BI models.

 

Best regards,

View solution in original post

2 REPLIES 2
DataNinja777
Super User
Super User

Hi @jj1 ,

 

For Item 1, where you need to return the value when Date Created is in January 2024 but Date Mailed is not in January 2024, the best approach is to work with a proper date table. First, create a date table covering the relevant range:

DateTable = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Year-Month", FORMAT([Date], "YYYY-MM")
)

This date table can be used to build slicers and ensure consistent filtering across multiple tables. Although in this case, you're comparing two separate date columns in the same table, the measure itself can still use the logic with formatted values:

Measure_Jan2024_Created_NotMailed :=
CALCULATE(
    SUM(Sample[Measure]),
    FILTER(
        Sample,
        FORMAT(Sample[Date Created], "YYYY-MM") = "2024-01"
            && FORMAT(Sample[Date Mailed], "YYYY-MM") <> "2024-01"
    )
)

This measure will return the sum of Measure where the Date Created falls in January 2024 but the Date Mailed does not.

For Item 2, where selecting a month should return both the Sales value from Table1 and the Refund value from Table2 for that month, it is better to use a shared DateTable rather than a disconnected month list. After creating the DateTable, set up relationships from DateTable[Date] to both Table1[Date] and Table2[Date].

Then, instead of using TREATAS, you can simply create measures that leverage the filter context provided by slicers based on the DateTable:

Sales_Selected :=
CALCULATE(
    SUM(Table1[Sales])
)
Refund_Selected :=
CALCULATE(
    SUM(Table2[Refund])
)

These measures will both respond to any filters applied via the DateTable, such as a slicer using DateTable[Year-Month]. This approach is more scalable and avoids issues with inconsistent sorting or ambiguous text-based month matching, making it much more suitable for professional, production-grade Power BI models.

 

Best regards,

Jaywant-Thorat
Regular Visitor

Question 1: Measure for Date Comparison Between Two Columns

You want to return a value only if Date Created is in Jan 2024 and Date Mailed is not in Jan 2024.

Assume your table is named 'Sample', and the value to return is in the Measure column.

DAX Measure:
MailedAfterCreatedMonth =
CALCULATE (
     SUM ( Sample[Measure] ),
     FILTER (
                  Sample,
                  FORMAT ( Sample[Date Created], "YYYY-MM" ) = "2024-01" &&
                  FORMAT ( Sample[Date Mailed], "YYYY-MM" ) <> "2024-01"
       )

)

This will help you to Filters only rows where:

Date Created is in Jan 2024, and

Date Mailed is not in Jan 2024.

Returns the sum of Measure for those rows.

 

Helpful resources

Announcements
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.