Running into some issues creating a sales report in Power BI. My company has a 13 period year with 28 days per period. I have managed to figure this out with a custom calendar and can get current/prior year sales by period. The next issue I have is our multiple data sources. Our day to day sales data comes from our POS reporting database, but our AP team will publish final sales numbers at the end of every period. I currently have POS data in one query (by day) and our AP data in different query (by period). The challenge is that I need to do a yearly sales report that shows current and prior year sales. I would prefer to be able to show all years, so I need 2018 sales using our AP data source and a 2019 YTD compared to prior year sales that uses both AP & POS data. This will have to be a combo of AP data for periods that have closed and the POS data for the current period and same period last year. So I will need the POS data for the current period (2019 - 13) and the prior period (2018 - 13) while also having the AP data for 2018 yearly sales for 2018 - 13.
Here are my ideas:
1. Merge the two sales queries to show only AP data for closed periods and POS data for the current period and same period last year. I would need to remove 2018 yearly sales from this report, but I could add it to a different visualization.
2. Append the two sales queries and use a DAX function (some kind of case function, need to learn more about switch) to select POS data or AP data depending on the calendar table. Could maybe duplicate the calendar table and have a dateset for this year and last year so i can select POS & AP data for 2018 - 13 depending on if it is This year or Last year.
This is not enough information for a full solution but any advice or links to articles would be appreciated as I cannot find much on google and do not know enough about Power BI.
Here are my ideas:
1. Merge the two sales queries to show only AP data for closed periods and POS data for the current period and same period last year. I would need to remove 2018 yearly sales from this report, but I could add it to a different visualization.
2. Append the two sales queries and use a DAX function (some kind of case function, need to learn more about switch) to select POS data or AP data depending on the calendar table. Could maybe duplicate the calendar table and have a dateset for this year and last year so i can select POS & AP data for 2018 - 13 depending on if it is This year or Last year.
This is not enough information for a full solution but any advice or links to articles would be appreciated as I cannot find much on google and do not know enough about Power BI.
