I have a power pivot to with daily sales tables and date reference table. The user inputs the date and I need to add a column that calculate the sales to that date. The date is refreshing to the date reference table and my formula is:
=if(Sales_report_[Doc_Date]<=RELATED('Date Reference'[Date]),Sales_report_[Gross Sales],0)
So in the above I need gross sales amount to show if the date of the sales (Doc_Date) is less or equal to the date on the reference table (Date Reference'[Date]); otherwise, show 0.
It is working but only shows gross sales for the dates within the same month. In other words, if the date reference is 02/28/2019 then it shows gross sales only for the rows within February 2019 but not January 2019. It is like not including the entire month in the if formula.
Hi Andrea,
Remember DAX measures also use row and column context to filter. Do you have any month/date fields in the PivotTable that could be applying the filter to only show February data?
Mynda
Yes, I'm comparing to a date. The data model has a date reference table that populates from the user input on the spreadsheet.
=if(Sales_report_[Doc_Date]<=RELATED('Date Reference'[Date]),Sales_report_[Gross Sales],0)
The formula is looking to the 'Date Reference'[Date] table.
The formula is working. However, it will only give me the rows for the same month. In other words, it will show only April for example if the date reference= 04/30/2020 but the formula is to show less or equal to April 30, 2020 so I'm expecting January 1 thru April 30th to show.
Hi Andrea,
You haven't answered my question. I asked what fields you have in your PivotTable that contain a date. It would help if you shared your file or at least some screenshots.
Mynda
attached file with screenshots.
