Forum Discussion
Helper ICalendar Table and Total Sales Measure
Hi All,
I'm trying to create a simple talbe using Date, Total Sales and Total Sales from last year using the "Sameperiodlastyear" function.
My source data is an excel that that has a number of columns. 2 of these are Date Columns: Date Shipped and Dated Invoiced.
I have have a Sales Column.
I have created a Calendar Table. the Date Range is from 1-Jan-2022 to 31-Dec-2024
I have created a Measure for Total Sales = Sum('Excel Data'[Sales]) I have created another Measure for
Total Sales PV = Calculate ([Total Sales] , SAMEPERIODLASTYEAR('Calendar'[Date]))
when I drop the Date into a table, I get every date bewteen the range. When I add my Total Sales Measure, I get this:
This is the date and amount of the last row in my excel data. why is this doing this?
Hi FormworkFan
If we assume we are talking about Total Sales as sales per day displayed in the table, please ensure the following:
1. Relationship between the sales table and calendar table :2. The data type of both dates is date and not date/time in one of them:
3. The calendar table is marked as a date table:
If all these are correct the formulas should work :
The pbix with my example is attached, you can follow the steps.
If it is still not workingplease share a pbix or some dummy data that keeps the raw data structure with expected results. It should help us clarify your scenario and test the coding formula.
How to Get Your Question Answered Quickly
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
The issue you're experiencing could be due to a mismatch between your date fields in your fact table (Excel data) and the Calendar table. Here's what might be happening:
Relationship Issue: Ensure that your 'Calendar' table is properly related to the date field in your fact table (Excel data). Typically, you would want the 'Calendar'[Date] column to have a one-to-many relationship with a specific date column in your Excel data (either 'Date Shipped' or 'Date Invoiced'). Without this relationship, the Total Sales measure might not aggregate correctly across dates.
Date Context: If you are using the 'Calendar'[Date] in your visual and it shows every date in the calendar, but your Total Sales measure pulls from the last row of your Excel data, it suggests the measure isn't being filtered correctly by the date context. This could happen if there's no active relationship or if the wrong date field is being used.
Suggestions to Fix:
Check Relationships: Go to the "Model" view in Power BI and verify the relationship between your 'Calendar' table and your fact table (Excel data). Ensure that the 'Calendar'[Date] is linked to the correct date column in the Excel data (e.g., 'Date Shipped' or 'Date Invoiced').
Adjust Date Field: Make sure you are using the correct date field in your model. For example, if you want to calculate sales based on the 'Date Invoiced', ensure this is the field linked to the 'Calendar' table.
Filter the Calendar Table: If you are only seeing the total sales from the last row, try adjusting the date column to filter correctly. Use a visual filter or ensure that the data is filtered correctly by date in your visual.
After checking the relationships and ensuring that the date context is applied correctly, your "Total Sales" and "Total Sales Previous Year" measures should work as expected.
2 Replies
- Ritaf1983
Super User
Hi FormworkFan
If we assume we are talking about Total Sales as sales per day displayed in the table, please ensure the following:
1. Relationship between the sales table and calendar table :2. The data type of both dates is date and not date/time in one of them:
3. The calendar table is marked as a date table:
If all these are correct the formulas should work :
The pbix with my example is attached, you can follow the steps.
If it is still not workingplease share a pbix or some dummy data that keeps the raw data structure with expected results. It should help us clarify your scenario and test the coding formula.
How to Get Your Question Answered Quickly
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Shravan133The issue you're experiencing could be due to a mismatch between your date fields in your fact table (Excel data) and the Calendar table. Here's what might be happening:
Relationship Issue: Ensure that your 'Calendar' table is properly related to the date field in your fact table (Excel data). Typically, you would want the 'Calendar'[Date] column to have a one-to-many relationship with a specific date column in your Excel data (either 'Date Shipped' or 'Date Invoiced'). Without this relationship, the Total Sales measure might not aggregate correctly across dates.
Date Context: If you are using the 'Calendar'[Date] in your visual and it shows every date in the calendar, but your Total Sales measure pulls from the last row of your Excel data, it suggests the measure isn't being filtered correctly by the date context. This could happen if there's no active relationship or if the wrong date field is being used.
Suggestions to Fix:
Check Relationships: Go to the "Model" view in Power BI and verify the relationship between your 'Calendar' table and your fact table (Excel data). Ensure that the 'Calendar'[Date] is linked to the correct date column in the Excel data (e.g., 'Date Shipped' or 'Date Invoiced').
Adjust Date Field: Make sure you are using the correct date field in your model. For example, if you want to calculate sales based on the 'Date Invoiced', ensure this is the field linked to the 'Calendar' table.
Filter the Calendar Table: If you are only seeing the total sales from the last row, try adjusting the date column to filter correctly. Use a visual filter or ensure that the data is filtered correctly by date in your visual.
After checking the relationships and ensuring that the date context is applied correctly, your "Total Sales" and "Total Sales Previous Year" measures should work as expected.