How to make a DAX calculation return BLANK rows
I'm working on a Power Bi template for my company. Since we are a global comapny i added Time Zone tables that help the end-user filter the visual for their Time Zone. Now I'm trying to create a DAX measure that will get the values in a DATE/TIME type column and sum the given UTC Offset in Hours, however I'm having a hard time when there's no value in a give row, DAX will just either not return the rows with blank or return the offset. What I'm trying to achieve is a function that will ignore the blank values and still return them, and when there is a value actually SUM the End Time UTC with the Offset. Note: [Current UTC Offset] is a measure that returns a decimal number from -12 to +12 How can i achieve this?
Displaying a Row as Fixed, when it's value needs to be manipilated
*I have trouble converting the data type of Column Amount in Source ABC to Decial/Numbers for this tutorial(help needed), see data source:https://drive.google.com/file/d/1VZJv8sPPYPcLiRKyO1fkEqbcsX-dz10P/view?usp=sharing . PBIX:https://drive.google.com/file/d/1wfjbLI7nBLQU5-Qs5rGHOL2XP4uc0UR9/view?usp=sharing I am trying to include rows in a Dax calculation from a mapping table(Pivot) to a fact table(ABC). The DAX is going to look at specific rows and bring those forward. The big issue is some rows are not present in the Fact table. I also need those rows to be displayed but their values to be manipulated. An example is row "Interco capital returned", which is not in the Fact Table(ABC), it should display a fixed value of "678" TotalAmountForSelectedItems5 = CALCULATE( SUM('ABC'[Amount]), // Corrected Column Reference 'ABC'[Roll_Up_Function] IN { "Cash flow from ops - management", "Cash flow from trading", "Depreciation and amortisation", "IPEP expense", "Disposals & impairment of fixed assets", "Profit on disposal of pooling equipment", "Scrapped pooling equipment", "Impairment or valuation adjustment of pooling equipment", "Disposals or valuation adjustments of other fixed assets", "Other cash flow from trading adjustments", "Share-based payments expense", "Working capital mvts incl. provisions", "Working capital mvts excl. provisions", "Debtor movements", "Creditor movements", "Inventory movements", "Prepayment movements", "Provision movements", "Change in capex creditors", "Change in loss compensation balances", "Interco interest and guarantee fees", "Interco cash flows", "Interco royalties", "Statutory reallocations", "Internal restructuring", "Interco dividends Total", "Change in interco balances", "Change in interco recharge clearing", "FX on interco debt", "Interco capital returned" } ) amitchandak jpessoa8 lbendlin
Help me create this graph
Hi, I'm trying to recreate this chart here. But I'm failing and only get this one: My summer hire column is found in my data table. I dragged it to my legends. Here's are my DAX formulas for more context: Retained 3M = VAR PeriodStart = MIN('Calendar'[Date]) VAR PeriodEnd = MAX('Calendar'[Date]) RETURN CALCULATE( COUNTROWS(Employment), Employment[Hire Date] >= PeriodStart && Employment[Hire Date] <= PeriodEnd, ( ISBLANK(Employment[Termination Date]) || Employment[Termination Date] >= EDATE(Employment[Hire Date], 3) ), CROSSFILTER('Calendar'[Date], Employment[Hire Date], None) ) Retention 3M = DIVIDE([Retained 3M], [Cohort Size], 0) Cohort Size = CALCULATE( COUNTROWS(Employment), Employment[Hire Date] >= MIN(Calendar[Date]) && Employment[Hire Date] <= MAX(Calendar[Date]) ) I created a disconnected table and create retention % as measure: Retention % = VAR SelectedPeriod = SELECTEDVALUE(RetentionAxis[Period]) RETURN SWITCH( SelectedPeriod, "3M", CALCULATE([Retention 3M]), "6M", CALCULATE([Retention 6M]), "12M", CALCULATE([Retention 12M]), "24M", CALCULATE([Retention 24M]), BLANK() ) RetentionAxis = DATATABLE( "Period", STRING, "Order", INTEGER, { {"3M", 1}, {"6M", 2}, {"12M", 3}, {"24M", 4} } ) Please help!
SELECTEDVALUE measure from slicer keeps returning blank value when used on another measure
So I have this simple dropdown slicer which is a list containing only a couple of dates, which in turn comes from a single-column table currently with NO relationship to either the date and the fact tables (CalData and Contas_a_Pagar respectively) in the semantic model: Then I created the following measure that picks up whatever choice I made on the aforementioned slicer: Data-base = SELECTEDVALUE(Data_Base_Lista[Data-base]) And when I drop this measure on a card visual it works just fine, so the measure is in fact picking up my date slicer choice: PROBLEM: However, when I define and evaluate the same measure on DAX query view, it returns BLANK: ↓↓↓↓↓↓↓ The problem is that I'm using this measure as a base date to calculate other measures, for instance to calculate the total of invoices whose due dates are on or after the selected base-date, and this measure is instead returning the grand total of all invoices (it should be $1,535,135.96 not $5,475,597.89 as shown below), and the reason is because the measure 'Date-base' is BLANK, so this other measure ends up picking up all invoices since their due dates are all on or after a blank date: Conversely, when I setup another measure that is supposed to calculate the total of invoices whose due dates are before the selected date, this measure also returns BLANK, instead of the correct total amount of $3,940,461.93, since this measure can't find any due date that is prior to a blank base-date: QUESTION: I know this is a basic stupid issue, but I just can't seem to figure out a way to fix this, even after having watched several videos about the SELECTEDVALUE function. What am I doing wrong here? Thanks, LeonardoDynamic Actuals with Forecast
Hi, I´ve been struggling with this for approx. two weeks now so hopefully someone here can help me. Problem: I have a table (which is combined by 2 separate tables) FC_GL_Table which is Forecasttable + GL Table(actuals). I´ve tried to simplify the Data table as below. As I´ve tried to show below is that when I select ForecastID = FC2501 I want GL from 2025-01-01 -> 2025-02-28, so basicily < 2025-03-01 and Forecast_part = GL (as shown in the support table) And then take Amount where FC part = FC and Forecast_ID = SelectedValue(ForecastID). A forecast have values for 13 months ahead. TLDR: Need a measure or solution so I can have Actuals + Forecast based on which Forecast_ID I choose. And to support this table I have this map table: I will provide the measure I´ve been playing around with. Forecast_Amount = VAR SelectedFCStartPeriod = LOOKUPVALUE(FC_StartPeriod[FCStartPeriod], FC_StartPeriod[Forecast_ID], SELECTEDVALUE(FC_GL_TABLE[Forecast_ID])) VAR SelectedYear = LOOKUPVALUE(FC_StartPeriod[FCStartYear], FC_StartPeriod[Forecast_ID], SELECTEDVALUE(FC_GL_TABLE[Forecast_ID])) VAR Forecast = CALCULATE( SUM(FC_GL_TABLE[Daily_Amount]), FC_GL_TABLE[Forecast_ID] = SELECTEDVALUE(FC_GL_TABLE[Forecast_ID]), FC_GL_TABLE[FC_Part] <> "GL" ) VAR Actuals = CALCULATE( SUM(FC_GL_TABLE[Daily_Amount]), FC_GL_TABLE[FC_Part] = "GL", FC_GL_TABLE[Date] < SelectedFCStartPeriod, ALLSELECTED(FC_GL_TABLE) ) RETURN Actuals + Forecast And this measure wont show any of the actuals, just the Forecast amount, unfortunately (FCStartPeriod = FCStartDate) Let me know if I need to add additional information. Kind regards, Rasmus
