Date Formatting & Date Comparison - Formatting and DAX Help

[irnm8dn]

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.

[Vvelarde]

Hi @irnm8dn

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.

[irnm8dn]

@Vvelarde @Sean

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.

[irnm8dn]

@Sean @Vvelarde

Any additional insight you may be able to provide?  Really struggling applying the solution and making the connection in the model.

Thanks again.

[Vvelarde]

@irnm8dn

Can you post your entire Window (Relationship), I saw in your screenshot that the relationship is made, why you want to change that.

[irnm8dn]

@Vvelarde

Happy to post whatever information you need, and thank you again for the help.  

It would appear that the relationship doesn't work the same way as in your video - and I cannot figure out why.  I think it is probably with the Date Table I created.

[Anonymous]

@irnm8dn Try changing your cardinality from One:Many to Many:One instead. The order in the cardinality is the order the tables are listed in the relationship. In your case, the date table is second, so you want the cardinality with the "One" on the right hand side.

[irnm8dn]

@Anonymous

Thanks I made the change.  Unforunatley, this is the informaiton that the slicer ouputs.

I don't know why the slicer is limiting it to these 2 options (fig. A), the dataset itself is clearly more expansive. (fig. B)

fig. A

(fig. B)

 

[Anonymous]

@irnm8dn could you post your .pbix so we can take a look? It will be hard to troubleshoot without looking into the data model in Power BI.

[Vvelarde]

@irnm8dn

The cardinality is:

DateTable - One   to Many DateREF

With this the error dissapear and relationships will work.

[Sean]

@irnm8dn

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

Does anyone know - is there a limit on how many conditions can be entered in the Conditional Column UI in the Query Editor?

@Vvelarde, @ImkeF, @MarcelBeug

[Vvelarde]

@Sean

How can i create the animated image(gif)? Thanks

[Sean]

@Vvelarde

http://www.screentogif.com/