Thanks for anyone that takes the time to read this, apologies it's long. First time posting. I am new to Power BI. I am creating a report which will show the number of orders of a product per quarter. I have date fields (year, quarter) a count of a field (to show the number of orders), a field for Previous Year, YTD, YTD this time last year, Bgt, F'cst. Bgt and F'cst are imported in via CSV files while most others are measures. The issue I am having is;
The report needs to use academic quarters and year, where Sep-Nov30 =Q1 Dec-Feb=Q2 Mar-May31=Q3 Jun-Aug31=Q4.
Thank you for the response. I have mocked up some data in excel for how it looks.
Main Table (data comes from a data mart)
The colour key is just a reference for you know what the fields are, they are of no importance to the actual report or layout
The orders for future dates will increase as the orders can be made a year in advance (this data pulls through from a data mart)
I also have a date table which I created. It has the usual fields (date, month, year, quarter, month number etc) plus the specific academic fields that I created (dax code for those are in the orginal post).
There are also other csv files imported but those are merely for further breakdowns into categories. They arent used in any formulae so I dont think they need to be mentioned.
I can post the code for the measures if you would like, most of which were found on this forum. The 4 variance measures are simply one measure divide by the other minus 1
Thank you for your response, this has sorted out the YTD in the academic format. However I still cannot get Previous year or YTD Previous year to work for the academic year. I already have academic year, academic quarter in my calendar table. as stated before, PY workas for calendar year but not academic year. I have tried watching videos to see for a solution but alas no luck, any ideas?
I appear to have the measures working now. when showing the measures alongside the dates they work. However when I try to display the measures alongside the categories it wont display, which I assume is due to their being no relationship between fact table and the measures. I thought maybe the way around would be to build the measures into calculated columns into the fact table, however it doesnt seem possible to have the same measures as calculated columns. Is this the case or am I just doing something wrong?
>>which I assume is due to their being no relationship between fact table and the measures.
If your tables not contains relationships and you also remove that category column, it may caused the issue.
In my opinion, I'd like to suggest you can add allselected(table[Category]) or values(table[Category]) as filter condition to your original formula to help calculate.(these function can get data from slicer of other filters even if your tables not contains relationships)
Community Support Team _ Xiaoxin Sheng If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
For learning resources/Release notes, please visit: | |
Thank you, it may be something I need to do at some point. But thankfully I have the measures somewhat working now. It seemed that the issue was to do with the cross functionality of the relationships. The date table to fact table (table from data mart) had to be single which sorted the measures while the fact table to csv files had to be both.
Apologies but I have one last question/problem.
I have an issue with my measures for the Q1-2019 values (which would represent the date range 1st september 2018 - 30th November 2018). While PY and YTD PY work perfectly for comparing 2017 and 2018 academic years, Q1-2019 seems to give random values. E.g. for Q1-2019, PY should equal Q1-2018, but it doesnt (works perfectly for Q1-Q4 of 2018, bringing back Q1-Q4 2017 respectively). It should be worth noting that for most categories and such there are no values for Q1-2019 yet, some do have a small amount of records however. Could this be effecting it?
Are you aware of what could be the issue?
Apologies for all the questions, I am very appreciative of all the help.