Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Experts,
I have data sourced from two tables. I want to pull the data on a table/matrix visual from both the tables grouped by fortnight end dates, based on fortnight period range selected on the data slicer. Something like this:
One of the tables already has fortnight end dates(say table1), but the other (say table2)doesn't. So, I added two new columns (week = weeknum(table2 [date]) and fortnight = cieling(week/2,1)to Table2, to calculate fortnight end dates.
But I don't get the fortnight end dates same as fortnight end dates of table1. Hence, the totals I pull from table 2 on the report are incorrect.
Please find link to sample pbi file: Please note in the file, Table (4) is Table2 from above scenario.
https://1drv.ms/u/s!Ag919_pO_UKrbwbzdOgVZ28Lhtk?e=OvrwoC
Really hope some expert can help me out.
Thanks in advance,
Meena
Solved! Go to Solution.
Hi @Anonymous ,
The column of "service date" is text. You need to change it to date. I created a new table that is the same as table(4). The calculation result is different from your expected result because of the data type. Please check if it is what you want.
last date = CALCULATE(MAX('Dim week ending'[Week ending]),FILTER('Dim week ending','Dim week ending'[Week ending] <= EARLIER('Dim week ending'[Week ending])-1))
Amount = CALCULATE(SUM(Table1[amount]),FILTER(Table1,Table1[Service date] <= MAX('Dim week ending'[Week ending]) && Table1[Service date] > MAX('Dim week ending'[last date]) ))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
The column of "service date" is text. You need to change it to date. I created a new table that is the same as table(4). The calculation result is different from your expected result because of the data type. Please check if it is what you want.
last date = CALCULATE(MAX('Dim week ending'[Week ending]),FILTER('Dim week ending','Dim week ending'[Week ending] <= EARLIER('Dim week ending'[Week ending])-1))
Amount = CALCULATE(SUM(Table1[amount]),FILTER(Table1,Table1[Service date] <= MAX('Dim week ending'[Week ending]) && Table1[Service date] > MAX('Dim week ending'[last date]) ))
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The tables are not joined hence filter will not work. There two ways you can try. Create a date table ( calendar)with a fortnight end date and join it both tables on a date.
2nd you can try like this. As of now, it not working because columns like service date or fortnight date are not date columns
New Amount = var _min_date =min('Table'[Date]) var _max_date =min('Table'[Date]) return CALCULATE(SUM('Table (4)'[amount]),'Table (4)'[Service date] >=_min_date && 'Table (4)'[Service date] <=_max_date)
Thanks for replying @amitchandak, but even if I join the two tables the solution doesn't achieve the functionality I am after.
Check this
https://www.dropbox.com/s/0dl5xi0hxfhu6rw/Sample.pbix?dl=0
Created a common time table. Changed the date format to date. Joined the data tables with the new dim.
As of now, dim is limited by the dates you had in Table(4). So if you have all the dates it should work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |