Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi,
I need a help in creating below two columns using DAX
I have a calculated column for "Net Change in Cost $", and using this column I have to create below two columns
1. Net Change in Cost $ - Full Date
2. Net Change in Cost $ - Last 4 Weeks
These two columns are based on Invoice Date visual filter
1. Net Change in Cost $ - Full Date
In this we have to use all transactions that fall within date range specified by Invoice Date filter and sum Net Change in Cost $ column.
Eg - if Invoice Date filter is set to 1/2/2021 ~ 11/30/2021, use all 2021 transactions
2. Net Change in Cost $ - Last 4 Weeks
In this we have to use transactions with Invoice Date within the 4 weeks leading up to end date which is specified by Invoice Date filter.
Eg if Invoice Date filter is set to 5/1/2021 ~ 12/24/2021, the end date is 12/31/2021 so include transactions with Invoice Date between 11/26/2021 and 12/24/2021
Also, if Invoice Date filter is set to 4 weeks, then both these columns should have same value.
Any help on this will be appreciated.
Solved! Go to Solution.
Dear @apatwal
Columns do not have access to the filter context and hence cannot read any filter. However, you can achieve the same by creating two measures as the values of a matrix visual sliced by transaction number or any other column.
Supposing that the date filter will come only from the slicer (meaning it is not part of the visual) then the first measure can be:
Net Change in Cost $ - Full Date =
CALCULATE (
SUM ( 'Table'[Net Change in Cost $] ),
ALLSELECTED ( 'Table'[Invoice Date] )
)
For the 2nd measure try
Net Change in Cost $ - Last 4 Weeks =
VAR EndtDate = MAX ( 'Table'[Invoice Date] )
VAR StartDate = EndtDate - 28
VAR Result =
CALCULATE (
SUM ( 'Table'[Net Change in Cost $] ),
FILTER (
'Table',
'Table'[Invoice Date] < EndtDate
&& 'Table'[Invoice Date] >= StartDate
)
)
RETURN
Result
Please let me know if works. If so kindly mark my reply as accepted solution. Thank you!
Hi @apatwal ,
Could you share me a sample .pbix file for test? Please don't contain any sensitive information.
Reference: How to provide sample data in the Power BI Forum - Microsoft Power BI Community
Best Regards,
Icey
Dear @apatwal
Columns do not have access to the filter context and hence cannot read any filter. However, you can achieve the same by creating two measures as the values of a matrix visual sliced by transaction number or any other column.
Supposing that the date filter will come only from the slicer (meaning it is not part of the visual) then the first measure can be:
Net Change in Cost $ - Full Date =
CALCULATE (
SUM ( 'Table'[Net Change in Cost $] ),
ALLSELECTED ( 'Table'[Invoice Date] )
)
For the 2nd measure try
Net Change in Cost $ - Last 4 Weeks =
VAR EndtDate = MAX ( 'Table'[Invoice Date] )
VAR StartDate = EndtDate - 28
VAR Result =
CALCULATE (
SUM ( 'Table'[Net Change in Cost $] ),
FILTER (
'Table',
'Table'[Invoice Date] < EndtDate
&& 'Table'[Invoice Date] >= StartDate
)
)
RETURN
Result
Please let me know if works. If so kindly mark my reply as accepted solution. Thank you!
Hi @tamerj1
Thanks for your reply!
I have created below two DAX measure:
1st Measure:
Net Change in Cost $ - Full Date Range =
var startdate = MIN('table'[Date])
var enddate= MAX('table'[Date])
RETURN
CALCULATE( SUM(table[Net Change in Cost $]),
DATESBETWEEN('table'[Date],startdate,enddate) )
2nd Measure:
Net Change in Cost $ Last 4 Wks =
var startday = MAX('table'[Date])-28
var endday = MAX('table'[Date])
return CALCULATE( SUM('table'[Net Change in Cost $]),
DATESBETWEEN('table'[Date],startday,endday) )
The Date is coming from Date Slicer created in report.
Let me know if I am missing out something here.
Hi @apatwal
You cannot use time intelligence functions without a standard Date table. Have you tried my approach?
Hi @tamerj1 ,
I even tried using your approach, which gives me same result as of mine DAX.
But I came to know that time intelligence functions have pre-requisite that Date table should be available in model.
Just want to know is there any article or any resource which can suggest us any drawbacks or issues that arises if we use Time intelligence when we don't have standard date table?
Hi @apatwal
Time intelligence functions are not relyable. I would prefer to go for manually coded formulas. However, I believe I can help you out but you got to give me some insite of what you're doing and what results are you getting with regard to both measures.
Hi @tamerj1
I do not have any date table.
There is only one table which includes all values including dates.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |