Forum Discussion
Date Formatting & Date Comparison - Formatting and DAX Help
I have a data set which has 3 columns, all with date relevant informaiton for my report. I am trying to understand how to best format this information so that Power BI will allow me to compare Annual, Quarterly and Monthly Results.
I have provided a sample data set for anyone who want to take this for a test drive.
https://www.dropbox.com/s/ru4bnd55noqccef/Sample%20Date%20Sheet.xlsx?dl=0
I would like to do Quarter vs. Quarter, Year over Year, and Month over Month Analysis as well as write a DAX statement that shows the difference between the time periods.
Any assistance with the formatting of the data recommendations on how to compare the different time periods would be greatly appreciated.
Thank you.
12 Replies
- Vvelarde
Community Champion
Hi Anonymous
Follow this video to transform your column in a date column.
After that use this date column to relation with a calendar table and made your dax to compare years, quarters, month, etc.
SeanAnonymous
first create the DateRef column as per Vvelarde's video
then its much easier to get the month number as shown below...
Add Column tab => Date => Month
BTW I do like the Conditional Column Interface its just that the above is much easier in this case :smileyhappy:
Does anyone know - is there a limit on how many conditions can be entered in the Conditional Column UI in the Query Editor?
This is great information, very helpful.
The only remaining part I am having difficulty with is table in which the relationship is made. In Vvelarde video.
I created a date table, but editing the relationships will not allow me to proceed. Also, notice the "line" between the two tables, it does not look like the one seen in the video.
