Forum Discussion
DATESYTD function
Hi FBergamaschi ,
I realised that I had more than one table marked as date table. For example, I had both the Calendar and CalendarTbl marked as Date tables. As soon as I turned off Calendar as being Date table it worked!
Thank You
To answer your question I need to see the entire data model and I need answer to the following
1 - the [Revenue] measure calculated revenues of course, but what is the table where this calculation happens (the table containing the revenues lines)?
2 - is the above table connected both to Calendar and to CalendaTBL? With which kind of relationship? Using which field?
Best
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi FBergamaschi ,
1). In the diagram I have provided that shows my model, you will see a table called TradeTbl. This is where Revenue comes from. I don't have another table called Revenues. The TradeTbl is the only fact table.
2). This fact table is connected to my CalendarTbl using a 1-to-many relationship with a single filter flowing from the CalendarTbl to TradeTbl.
Thank You
Thank you but still you did not specify how Calendar is connected to the table TradesTbl
If the measure works using Calendar, there must be a relationhip between Calendar and TradesTbl that I do not see
Further, what colum connects Calendar to TradesTBL and what CalendarTbl to Trades Tbl?
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi FBergamaschi ,
Apologies, I did not make it clear.
There is no relationship between Calendar and TradesTbl.When I said the measure works when referencing Calendar I realised the values it was returning were simply the sum of revenue across all years i.e. basically just returning the sum of revenue which is not what I wanted.
The reason why I need to use CalendarTbl and not the original Calendar is because I need to append some calculated columns to Calendar but Calendar is coming via a direct query, so hence why I created a calculated table called CalendarTbl using SELECTCOLUMNS and then to this table I was able to add my calculated columns to help me further with my analysis. But this CalendarTbl, despite being marked as a DATE table and ensuring the date field is of the type "Date" and not "Date/Time" and also ensuring the foreign key in the TradeTbl is also of the type "Date" where these two tables connect, I am still not able to get any values return from my measure.
Thank YouOK, can you please share the model? I need to check it. It should perfectly work, but I need to open it. I shall send you a provate message now.