IF value exists on calendar date
Hi everyone, i need help creating a measure that evaluates whether a value in on table exists at a particular date within the calender table . I am working with Two tables, on main table that has a customer and a purchase date. The second table is just a table showing the daily calendar dates. the outcome wanted is to evaluate whether a purchase was made by a customer at a certain date on the daily calender. the tables are as shown below : RESULT If a customer has purchased something on the date equivalent to the calendar date, return true, else false. as shown below: If anyone could assist me with writing a measure that could produce this outcome that would be wonderful 🙂 all help and sugegestions are welcome: Thank you
DAX formula to take 1 chart with today's date filtered and drill into another chart with -7 days
Hi all...so I have a chart, let's call it chart A. that chart is filtered on today's date because it's used as a monitoring chart for infrastructure incidents. I have chart B which is supposed to show past 7 days. Ideally, when something shows up in chart A that's bad, as it means it's a problem ONLY today. I have a filter set to incident shows up in table if remaining disk space is <20%. So let's say Server A shows up in Chart A. Below this, in Chart B, I want to see that server's prior 7 day disk usage. It does not work when I have Chart B set to relative date. In every case, when I click on a server name in Chart A, all it shows me in Chart B is today's data. I want to be able to see results (filtered on today's date) from Chart A and then drill in for values in Chart A to today's date -7 days for Chart B. Literally everything I have tried ends up in Chart B as only showing data for today's date. the other issue is I have a parent child hirearchy in 'Server', which has many child 'Disk Drives'. Violations that show up in Chart A are based on the storage space for 'Disk Drives', on today's date (Chart A). I want to be able to click on the Hostname field, and have ALL drives for that server show me today -7 in Chart B (the drilled in chart). Chart A (based on Nov 8th) Chart B ( Based on Nov 1-7th. these are separate values, used for illustrative not supposed to match) So Server 'A' has 5 disk drives. Today, one of them went to 0% free. Only for today was drive V in violation. I want to have Chart B show me 7 day history for ALL of server A's 5 drives. Thanks
X-Axis Custom Display of Start of the Each Month
How do I get the formatting of the X-axis like this: 1/2008, 1/2009, 1/2010 It is displaying 2008, 2012, 2014. That is not what I want. Each data point represents the start of each month of the year For example, 2008 will be Jan 1 2008, Feb, 1 2008 through Dec. and that repeats for 2009, 2010 etc. I'll upload my graph so you can see how the data is displayed.
DAX Calculated Column for PRE/POST Sequence in Power BI
I'm working with a Power BI model that includes a table called "All Depots". This is a large, granular table that includes details by customer and product. However, for this particular problem, I'm focusing only on the "Depot", "PRE/POST", and "Transaction date" fields. ## Objective I need to create a calculated column that assigns a sequence number to PRE and POST periods for each depot. The sequence should work as follows: - For PRE periods: - The last (most recent) PRE date should be assigned -1 - The second-to-last PRE date should be assigned -2 - The third-to-last PRE date should be assigned -3 - And so on... - For POST periods: - The first (earliest) POST date should be assigned +1 - The second POST date should be assigned +2 - The third POST date should be assigned +3 - And so on... - The sequence should reset for each depot ## Current Approach I've tried several DAX formulas, including variations of RANKX and COUNTROWS, but I haven't been able to achieve the desired result. Here's an example of one approach I've tried: PRE_POST_Sequence = VAR CurrentDepot = 'All Depots'[Depot] VAR CurrentDate = 'All Depots'[Transaction date] VAR CurrentPREPOST = 'All Depots'[PRE/POST] VAR PRE_Sequence = IF(CurrentPREPOST = "PRE", -RANKX( FILTER(ALL('All Depots'), 'All Depots'[Depot] = CurrentDepot && 'All Depots'[PRE/POST] = "PRE" ), 'All Depots'[Transaction date], , DESC ), BLANK() ) VAR POST_Sequence = IF(CurrentPREPOST = "POST", RANKX( FILTER(ALL('All Depots'), 'All Depots'[Depot] = CurrentDepot && 'All Depots'[PRE/POST] = "POST" ), 'All Depots'[Transaction date], , ASC ), BLANK() ) RETURN IF(ISBLANK(PRE_Sequence), POST_Sequence, PRE_Sequence) ``` ## Problem The current formula isn't producing the expected results. It's either not sequencing correctly or not resetting for each depot as needed. ## Question Can anyone suggest a DAX formula that would achieve the desired sequencing as described above? I'm open to completely different approaches if they can solve this problem more effectively. ## Additional Information - The "All Depots" table contains multiple rows per day per depot. - The "PRE/POST" field contains only "PRE" or "POST" values. - The "Transaction date" is a date field. Thank you in advance for any help or suggestions!
Dynamic Period Label
Hi Team! To the best of my abilities, I have researched this and unable to find a solution for my scenario. I have a DimDate and I have shared schema below. This is a 454 period calendar. I would like a Dynamic column via m-query with below logic: 1. Label current period = IF [TODAY] "P0" (use [PERIODSTARTDATE] and [PERIODENDDATE]) 2. Based on 1 above, determine "P-3", "P-2", "P-1", "P0", "P+1", "P+2", "P+3" and so forth The goal here is to aggregate dates into "PERIODLABELS" that represent the planning time horizons of our business. To help with context, this is how it would present in Excel: Help would be greatly appreciated! PERIODENDDATE FISCALPERIODSORT FISCALPERIOD FISCALQUARTER PERIODSTARTDATE FISCALQUARTERSORT FISCALYEAR DATES Year Quarter Week of year Start of week Start of month CALENDARQTRYEAR TODAY Week of fiscal year FISCALPERIODNUM Month name FISCALYEARSHORT PERIODSHORT PERIODNAME This Week 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 27 6/29/2015 ######## 2016-Q1 8/18/2024 1 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ######## 7/31/2015 0 Period 1 Q1 ######## 0 2016 ######## 2015 3 28 ######## ######## 2016-Q1 8/18/2024 2 1 Jul 16 P1 P1-Jul ########Count distinct start dates by item
Dear all, I have an issue with counting all distinct start dates per item (id). f.e. of the data ID date start or stop date 1 12/02/2024 start 1 14/02/2024 stop 1 15/02/2024 start 1 16/02/2024 stop 2 05/03/2024 start 2 05/03/2024 stop 2 05/03/2024 start 2 05/03/2024 stop 3 10/06/2024 start 3 10/06/2024 stop 4 10/06/2024 start 4 10/06/2024 stop i am trying to make a table that has following info (only the start dates per item - and total amount of start dates that each item has) ID test date start or stop date count amount of days tested 1 12/02/2024 start 2 1 15/02/2024 start 2 2 05/03/2024 start 1 3 10/06/2024 start 1 4 10/06/2024 start 1 I dont really need to start or stop column but i kept it here as en example. now i only have "1" everytime i try to calculate how many distinct dates there are, but if i remove the date column it does calculate correctly the distinctdates, with filter that start stop column = start. Count amount of days tested = CALCULATE ( DISTINCTCOUNT ( 'table'[test date] ) , FILTER ('table', 'table'[start or stop] = "start") ) Afterwards I want to be able to make a percentage with all the ID that have more then 1 (or only 1 test date) test date over ( / ) all ID in total. So i can see a percentage of how many ID were only tested once. how should change this calculation? kind regards
Last date in period issue when use DatesBetween
Hello, It's the first time I am posting a question here. Hopefully I do it right. I have a question with Last date issue when use DATESBETWEEN function hope someone can help with. I have a parameter table with parameter of 1-12 I also have a DimDate table with a InvoiceDate column and End of Month column. What I am trying to achieve is to calculate sales based on period. If the parameter is 2, then I want to evaluate sales every 2 month as a period. For example, If the [End of Month] is 02/28/2022, then current period is 01/01/2022-02/28/2022, last period should be 11/01/2021-12/31/2021. However when I use the DATESBETWEEN function to get the last period, the last date is not showing as I would like because the last date in each month is differen: the last date in the last period is 12/28/2021, instead of 12/31/2021. How can I get the correct last date for the last period? Thank you! First Date DatesBetween = FIRSTDATE( DATESBETWEEN( DimDate[InvoiceDate], DATEADD(FIRSTDATE(DimDate[InvoiceDate]),-(2*[Parameter Value]-1),MONTH) ,DATEADD(LASTDATE(DimDate[InvoiceDate]),-1*[Parameter Value],MONTH))) Last Date DatesBetween = LASTDATE( DATESBETWEEN( DimDate[InvoiceDate], DATEADD(FIRSTDATE(DimDate[InvoiceDate]),-(2*[Parameter Value]-1),MONTH) ,DATEADD(LASTDATE(DimDate[InvoiceDate]),-1*[Parameter Value],MONTH)))
Select and calculate all dates from slicer
I have a slicer of a date column but every day is on the 1st like Month-Year 1/1/2023 2/1/2023 3/1/2023 4/1/2023 5/1/2023 So I want the slicer to be a date hierarchy of Year and Month only: Then I have a measure to calculate a total of a column based off slicer selection, but it's not allowing me to select multple values. Online Revenue = VAR SelDates = SELECTEDVALUE( Table[Month-Year] ) VAR Revenue = CALCULATE( SUM( Table[Revenue] ), AND( Table[Month-Year] = SelDates, Table[Type] = "Online" ) ) RETURN Revenue
