Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have a few tables that looks like this:
Table 1
Date 1 | Value 1 |
1/1/2020 | 34 |
1/2/2020 | 76 |
1/3/2020 | 46 |
1/4/2020 | 84 |
Table 2
Date 2 | Value 2 |
1/3/2020 | 55 |
1/4/2020 | 35 |
1/5/2020 | 57 |
1/6/2020 | 25 |
1/7/2020 | 75 |
Ideally, I would like a table that looks omething like this:
Month | Date | Value 1 | Value 2 |
January | 1/1/2020 | 34 | |
February | 1/2/2020 | 76 | |
March | 1/3/2020 | 46 | 55 |
April | 1/4/2020 | 84 | 35 |
May | 1/5/2020 | 57 | |
June | 1/6/2020 | 25 | |
July | 1/7/2020 | 75 |
Is there a way for me to do this using DAX or in Query? Any help would be appreciated. Thank you!
Solved! Go to Solution.
Hi, @Anonymous , you can first merge the 2 data tables by date column by join kind of "Full Outer" in Power Query,
then tweak the data per your requirements.
You might want to refer to the attach file for details.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @Anonymous ,
Create a calendar table and create 1 - * or 1-1 relationships between the other two tables. Use the date field in the table visual.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous , you can first merge the 2 data tables by date column by join kind of "Full Outer" in Power Query,
then tweak the data per your requirements.
You might want to refer to the attach file for details.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi, will this work for live data that updates frequently? Thank you.
@Anonymous , Please find the attached file after the signature. In case it live connection have date table at source.
Hi it seems like I do not have the latest ver. of power bi and cant seem to download it
Hi @Anonymous ,
Create a calendar table and create 1 - * or 1-1 relationships between the other two tables. Use the date field in the table visual.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Create a date table and join on the date of both table. And create a visual using the date from the date table .
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi what do you mean by join on the date of both table and create a visual using the date from the date table? Thanks
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |