Forum Discussion
Monthly Calculations
Hi Anonymous ,
As far as I understand, try to do this:
1. I make some changes to your Fact Table.
2. I create a DimDate table, not using yours.
DimDate = CALENDAR ( MIN ( FactSilverfin[Monthname] ), TODAY () )3. Create relationship.
4. Create your measures and put them in Cards.
For more details, please check the attached PBIX file.
If I understand you incorrectly, please let me know.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Anonymous ,
navigating through time can become somewhat tedious without a dedicated calendar table, for this I recommend creating a dedicated Calendar table and also create a date column in your table from your Month column.
I assume that you can find almost everything about time calculations in this article by the people from sqlbi.com, even if it's a lengthy article it's a worthy read: https://www.daxpatterns.com/time-patterns/
Regards,
Tom
Hi TomMartens thanks for your answer! Will definitally look into the artice
Is there a difference between the Dimdate I have downloaded from the Kimball website and the dedicated Calendar table you mentioned? Also I have a date column in my facttable at the right,might be confusing as the column is named "MonthName" but this is a date column right?
Thanks
Hey Anonymous ,
I have no idea if there is a difference between the Calendar table you have downloaded and the Calendar table I mentioned. It's about having a data model (using a separate table) that follows the paradigm of star schema modeling. One aspect might be special to the Power BI data model, if you are using DAX time intelligence functions, these functions require that the Calendar table is complete meaning without gaps at the day level.
Regarding the column Monthname, I have no idea if the column is a date column, as I'm not familiar with your data model, but if this column has the data type date or datetime, maybe this column can be used to relate to the Calendar table.
Regards,
Tom
Regards,
Tom
Hi Tom
Yeah I have done this already,so that shouldn't be the issue here.
Hi Anonymous ,
As far as I understand, try to do this:
1. I make some changes to your Fact Table.
2. I create a DimDate table, not using yours.
DimDate = CALENDAR ( MIN ( FactSilverfin[Monthname] ), TODAY () )3. Create relationship.
4. Create your measures and put them in Cards.
For more details, please check the attached PBIX file.
If I understand you incorrectly, please let me know.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.