Need help with DAX for different fortnight dates from two tables

[Anonymous]

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

[v-xuding-msft · Accepted 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.

• Calculated column

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))

• Measure

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.

[amitchandak]

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)

[Anonymous]

Thanks for replying @amitchandak, but even if I join the two tables the solution doesn't achieve the functionality I am after.

[amitchandak]

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.