firstdate

8 Topics

Return the value for the next minimum date if the value field is empty, and group by ID.
Hi, In dax, I would like to return the value for the next minimum date if the value field is empty, which should be group by ID. Also, need to return the coresponding value in the associated category colum. I have only one table and have attempted to use several measures, the example measure below does not work well when there are null values. Please help! CALCULATE ( MIN ( Query1[value]), FIRSTDATE ( Query1[date]) )
denengland
3 years ago Place DAX Commands and Tips
1.8KViews
0likes
7Comments
Sum first date of the year & ignore slicer
Hello, I’m trying to get the sum of just the first day of the year of the applied slicer. But I can’t figure out how to use just the year of the slicer to get it done. In the CALCULATE() when I use just the ALLEXCEPT() I get the sum of all values and when I use the ALLEXCEPT() with FILTER() I get a blank result. I’ve searched for similar issues, but couldn’t find any answer that solve this problem.
Solved
r_fioretti
4 years ago Place DAX Commands and Tips
1.2KViews
0likes
3Comments
First date of last month in a logical comparison
I am using this expression in filter part of calculate function: D_Employee[RowActiveFrom] < FIRSTDATE(PREVIOUSMONTH('Calendar'[date])) However Power BI doesn't calculate it and gives this error: "A function 'FIRSTDATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed." I'm just trying to compare the first date with another date and don't understand what is wrong here. Is there something to be corrected or a workaround to be used?
Solved
Anonymous
4 years ago Place DAX Commands and Tips
582Views
0likes
1Comment
calculate not respecting its own filter
Hoping I can get some help here. I have a measure using another measure as a filter value to try to find the earliest date for which a column equals the filter value. I'm getting strange behaviour from the peakdate measure, however. I have a table: TABLE, with columns DATECOL and INDICATOR. The date range in this table is not continuous, but spans more than a year,and ends on the current day (and there are data for that current day). I have a measure, PEAK, that finds the maximum value of INDICATOR that falls within a date range. PEAK = calculate(maxx('TABLE', 'TABLE'[INDICATOR]),'TABLE'[DATECOL]>=date(2021,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01)) This measure seems to work fine - finds the peak value in that date range, and I can confirm that by visually inspecting values in the table. I have ANOTHER measure, DATEOFPEAK, that I want to return the earliest date in DATECOL which the PEAK is observed (in this case, it occurs on multiple days). DATEOFPEAK = calculate(min('TABLE'[DATECOL]),filter(all('TABLE'),'TABLE'[INDICATOR]=[PEAK]&&'TABLE'[DATECOL]>=date(2021,12,01)&&'TABLE'[DATECOL]<=date(2022,03,01)) This will not work, it returns the minimum date specified by the filter (2021,12,01) rather than the minimum date at which the PEAK value is observed in the INDICATOR column. I have ensured that the variables are formatted in the same way. In the original table (which is a calculated table, although I'm having the same problem whether my PEAK measure is based on a preexisting column, a calculated column, or a column in a calculated table), INDICATOR is rounded to 0 (in the actual column calculation in the summarize DAX command). I've tried swapping firstdate for min and I've tried restructuring as a MINX, and everything just returns the minimum date value specified in the PEAK measure but it's ignoring the part of the filter that directs it to filter INDICATOR down to only values that match PEAK. Please, what am I missing?
Solved
AEIOUandY
4 years ago Place DAX Commands and Tips
849Views
0likes
2Comments
First date base on non blank value of a specific column
Hi, I am having trouble writing a mesure that will get me the first date base on a non blank value of a specific column Here an exemple of my data: and the expected result would me something like that: Thanks for your help!
Solved
lnpapin
4 years ago Place DAX Commands and Tips
2.4KViews
0likes
2Comments
DAX: Creating labels with FIRSTDATE and a date filter or dropdown
Hi Everyone, I'm new to Power BI and can't figure out how to solve the following issue: I have a list of real estate assets that are valuated each quarter. Within the dashboard I want to be able to analyse all the valuation data for each quarter (recent and historical). One important aspect of the analysis is to flag an asset if it is new to the portfolio ('acquisition'). In order to do so, I want to use a slicer or dropdown to select the valuation date that shows the specific KPI's for that quarter and group assets as acquisitions when the date = firstdate of valuation date. I've tried a lot of solutions today, but all of them results in adding multiple extra rows to my table. Please see below a little mock-up in Excel and a sample of the data. Valuation Date Object ID Market value 31-12-2019 10101 104.900.000 30-09-2020 10101 110.354.800 31-12-2020 10101 114.437.928 30-09-2021 10101 113.407.986 31-12-2021 10101 122.253.809 31-12-2019 10102 101.900.000 30-09-2020 10102 104.039.900 31-12-2020 10102 111.946.932 30-09-2021 10102 120.231.005 31-12-2021 10102 128.526.945 31-12-2019 10103 137.138.250 30-09-2020 10103 107.600.000 31-12-2020 10103 112.334.400 30-09-2021 10103 123.343.171 31-12-2021 10103 123.096.485 31-12-2020 10104 105.200.000 30-09-2021 10104 104.674.000 31-12-2021 10104 105.930.088 31-12-2020 10105 106.500.000 30-09-2021 10105 108.204.000 31-12-2021 10105 110.259.876 31-12-2021 10106 96.200.000
Solved
Anonymous
4 years ago Place DAX Commands and Tips
783Views
0likes
1Comment
Scaling time series data based on starting date value
Hi all, I'm struggling with a probably relatively easy problem: I have time series data (e.g. stock prices) with 3 variables: date, stock name (Aktie) and value. In this example I have just 2 categories, which have very different values (first one around 1500 and the 2nd one around 10). So if I plot them on a simple line chart, it looks quite ugly as you don't see any movement. So the idea is to scale both values so that they start at the same value (say 1,0) and then indicate the trend compared to that value. This scaled time series can't be saved as a calculated column, because the plotted date period should be dynamic (set by a slicer). The result should look like this. I was able to achieve this plot by using the following measures: FirstValue = CALCULATE(SUM(Tabelle1[Wert]);FILTER(ALL(Datumstabelle);Datumstabelle[Date]=DATE(2020;1;1))) ScaledValue = DIVIDE(SUM(Tabelle1[Wert]);[FirstValue];0)a Unfortunately I wasn't able to set the DATE(2020;1;1) to the minimum value in the current filter context. Whatever I tried, PowerBI set the filter context to the date on the x-axis in the line chart, resulting in the scaledValue being 1 every day for both stocks. Any ideas?
Solved
IMett
6 years ago Place DAX Commands and Tips
2.9KViews
1like
6Comments
Firstdate producing a results with more than one date for a customer
Hi, may you kindly help me. My firstdate function is not producing my intended result.I want to see the first date the customer started to pay his monthly premiums and the amount the customer paid. But if I use firstdate function it's giving me all the dates that the payment was made and in so doing it's adding up all the amount paid. This is the syntax I'm using First Payment Date = FIRSTDATE('macroinsure col_trans_log'[tran_date]) So that tran_amount is an addition of all the transaction made by that client but what i require is transaction amount on that specified first date.Which is 90 see picture below
Tendai
6 years ago Place DAX Commands and Tips
2.3KViews
0likes
5Comments