User Profile
bsz412
Helper III
Joined 4 years ago
User Widgets
Contributions
Re: Line chart upper and lower on tooltip - why
hi Aniya, thanks for the answer! I could not find anything in the configuration, it might be that this feature is available in a previous version of pbi desktop... I dont know. I just deleted the chart and created it again, and now I do not see the upper and lower levels.fixing DAX formula - exchange rate
Hi, I need to convert local currency amounts to EUR by using the exchange rate of the selected end-year and the last available month of that year. I have a formula that works, but it does not use the same ex rate for each month: first column is a date column from a date table second is the local currency amount 3rd: result of my Vlookup ex rate conversion dax measure 4th: ex rate test: shows what ex rate the vlookup measure used the last 2 columns are the result of 2 measures: End month number = VAR _maxyear = MAX('year_selector'[year]) RETURN CALCULATE( MAX(purchases_by_week_vendor[month]), ALL(purchases_by_week_vendor), purchases_by_week_vendor[StartYear]=_maxyear ) End year number = MAX('year_selector'[year]) the 2 measures show the same, correct month and year in this table, however in the Vlookup dax measure these values are changing: Sell-in EUR Vlookup = SUMX ( purchases_by_week_vendor, VAR exchRate = LOOKUPVALUE ( exchange_rates[avg_rate], exchange_rates[source_currency_code], purchases_by_week_vendor[currency_code], exchange_rates[year], MAX ( year_selector[year]) , exchange_rates[month], [End month number] ) RETURN [Sell-in local currency (test)] * exchRate ) The issue is, that the year_selection table is connected to the date table. I cannot inactivate that relationship as the datamodel is used for several reports and I cannot change that relationship. As I see the formula is correctly choosing the year, but the month is not consistent in the monthly breakdown. If selected year is 2022, latest available month in the fact table is November, so the 2022 nov ex rate should be used for each and every month. Any idea how I could fix this formula? thank you !Same week of selected year cumulative data
Hi, I would need to calculate cummulative sell-in with weekly breakdown. I have a date table with year, weeknumber, start of the week date etc... The challenge is that there is a year slicer, and YTD sell-in max year should show cummulative data for max year (2022 in this case), and YTD sell-in min year should show cummulative data for the corresponding weeks for the selected min year (2018) Is it possible to create that measure? thank you!azure analysis services - good tutorial
Hi All, I would like to learn how to work with azure analysis services. I should move my power bi reports to AAS soon, and so far I did not really find a good practical tutorial on how to develop power bi reports in AAS, or how to move existing pbix datamodels to AAS. ANy recommendation? thank youselect column dynamically in dax measure - is it possible?
Hi, In my report I have a separate start and end filter for selecting time period. I have to calculate sellout amount to EUR, by using a sellout column in sales table and the exchange rate from the exchange rate table. The tricky part is, that if Aug 22 is selected, they want everything to be calculated with Aug 22 ex rate. If Jul 22 is selected as end period, they want everything to be calculated on that exchange rate. First I had an ex rate table like this: and it was connected with many to many relationship to the sales table by the country code column. Sales amount was calculated with the help of a lookup function. The disadvantage of the Lookup function is the performance. RELATED function would be much faster, but for that I would need a one to many relationship. Now, what I am trying to do, is that I modified the exchange rate table, pivoted it, so that I have the ex rates in different columns and I can now establish the one to many relationship to the sales table. I can use now the RELATED function, but as each month is in a different column, I would have to add each and every column in the code, in the switch function. (The code always have to use the exchange rate of the selected end period) Is it possible to reference the column name with a measure, instead of adding 50-60 lines of code, each month one by one? It should always use the column name that is identical with the selected end period. Thank you! Sellout Amount EUR test = VAR ex_ratedate = SELECTEDVALUE(Endperiod[yearmonth number]) RETURN SUMX( sales, sales[sellout]* SWITCH( TRUE(), ex_ratedate=202101, RELATED(exchange_rate[202101]), ex_ratedate=202102, RELATED(exchange_rate[202102]), RELATED(exchange_rate[202206])))
