DAX Measure for Weekly Conversion

[cr731]

I have two tables:

1. Transaction table containing a column dates and a column of values. The dates represent fiscal week ending dates.

2. Date table containing each week ending date and a conversion factor for that date.

I'm trying to write a measure which will divide by values column in #1 with the conversion factor in #2.

If I pivot my data by week, then the measure should divide my value by that week's conversion factor.

But if I pivot my data by month, then the measure should aggregate the conversion factors for that month (so if I am in week 3 of the month, it should add conversion factors for week 1+2+3 and divide by that).

The last stipulation is that in the MTD case, it should only aggregate up to the latest date of my transaction table. So if my data is only up through 3 of the month, my aggregated conversion factor should be up to week 3. But if it is a month in the past, then the total of that month's weekly conversions should be used.

I'm hoping I explained that well enough but let me know if not.

I'd like to have it be a dynamic measure rather than having calculated columns for each.

 

[Matt Allington]

so write a measure that sums the conversion factor. Something like
conversion to use=sum(date[conversion])

then add the transactions
Total Value=sum(Trans[value])

divide the second by the first,

Answer=Divide([total value],[conversion to use])

The only time it won't work is in a partially complete month if you have future dates in the calendar. So if you only load the calendar table up until the last data in your transaction table, then you are done. That is the easiest way to solve the current MTD issue. If this is not an option, you could change the last formula to something like this (which I think will work, but you should test it).

Answer=Calculate(Divide([total value],[conversion to use]),Trans)

where Trans is the name of your transaction table. You can read about how this formula works here Many to Many Relationships in DAX Explained - Excelerator BI