Same period previous N months
I want to calculate total sales for previous N months for the same period according to the slicer. Eg if in slicer I select range from 1-10-21 to 3-10-21 the my column chart should display total sales for previous N months within this range. I wrote following Dax measure for the same but not getting desired result: same period = CALCULATE(SUM(financials[ Sales]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -'Select N'[N Value],MONTH),FILTER(financials,AND(financials[Date]>=MIN('Date'[Date]),financials[Date]<=MAX('Date'[Date])))) Any suggestions are apprecited.
Adding Target Line for 2023 Cumulative Costs in Power BI Visual
Hello Power BI Community, I am seeking assistance with a visual I've created to compare cumulative costs for the years 2022 and 2023. For the visual, I have developed the following measure for cumulative costs: ```DAX Cumulative Costs = CALCULATE( SUM('Cost 1'[Amount EUR]), FILTER( ALL('Calendar'[Date]), // Remove the filter context from the Calendar table YEAR('Calendar'[Date]) = YEAR(MAX('Calendar'[Date])) && MONTH('Calendar'[Date]) <= MONTH(MAX('Cost 1'[Date])) ) ) ``` This measure is placed on the Y-axis, with the Calendar Month on the X-axis, and the Calendar Year as the legend. My query is about incorporating a target line for 2023 cumulative costs into this visual. Currently, the use of the legend for differentiating between the years is preventing me from adding a secondary Y-axis. Is there a way to work around this issue? Should I create a separate measure to facilitate the addition of the target line? I have attached a sample of the data and a screenshot of the visual for your reference. Any guidance you can provide would be greatly appreciated. Sample Visual and sample Dataset Attached: https://drive.google.com/drive/folders/1IOviSSW_LKRWuyKH_R7Gh6RaDDj6_zZV?usp=sharing Best Regards, Manish Tripathi
Dynamically show data from current selection in slicer till max year in data.
Hello Community! I have a business requirement where I have to show data as follows- Suppose I have year slicer, when I select a year from the slicer then it should show data from selected year till current year (i.e 2017) in this case. Value will be added up in each row as shown. If I select 2017 then it should show only 2017-18 data. It should be dynamically changing with the year selected in the slicer till max of year. Can anyone help and guide me in this? Thanks in advance!Creating DAX rules to display received and sent dates based on one date column
Hello Folks, I really need your help to solve this problem that I have. I have the following sample data related to tickets treated during week number 15 of the month of April 2023, I would like to display the received and sent dates based on the Date of Action column (and only display the dates related to week number 15 of April, I have created a relationship btw between the date table and Date of Action column, but some outputs are still not correct), then calculate the time difference between the two (if the result is zero then it is understood that the ticket treatment was achieved in less than a day). I have implemented the following DAX calculations (thanks to user P_d2023 ) to get the results I have shown in the Power canvas belo PERSON_TIMELINE = RANKX(FILTER(WEEKLY_IDs, WEEKLY_IDs[Ticket_Nb] = EARLIER(WEEKLY_IDs[Ticket_Nb]) && WEEKLY_IDs[Sender_Name] = EARLIER(WEEKLY_IDs[Sender_Name])), WEEKLY_IDs[Date_of_Action], , ASC, Dense) TICKET_TIMELINE = RANKX(FILTER(WEEKLY_IDs, WEEKLY_IDs[Ticket_Nb] = EARLIER(WEEKLY_IDs[Ticket_Nb])),WEEKLY_IDs[Date_of_Action], , ASC, Dense) From the above calculations, I created the three columns shown in the Power BI report below: RECEIVED_DATE = CALCULATE(MIN(WEEKLY_IDs[Date_of_Action]), ALLEXCEPT(WEEKLY_IDs, WEEKLY_IDs[Ticket_Nb]), WEEKLY_IDs[TICKET_TIMELINE]=WEEKLY_IDs[PERSON_TIMELINE]) REPLY_DATE = IF( ISBLANK(CALCULATE(MIN(WEEKLY_IDs[Date_of_Action]), ALLEXCEPT(WEEKLY_IDs, WEEKLY_IDs[Ticket_Nb]), WEEKLY_IDs[TICKET_TIMELINE] > WEEKLY_IDs[PERSON_TIMELINE])), WEEKLY_IDs[RECEIVED_DATE], CALCULATE(MIN(WEEKLY_IDs[Date_of_Action]), ALLEXCEPT(WEEKLY_IDs, WEEKLY_IDs[Ticket_Nb]), WEEKLY_IDs[TICKET_TIMELINE] > WEEKLY_IDs[PERSON_TIMELINE]) ) Treatment Duration = if(INT([REPLY_DATE])-INT([RECEIVED_DATE]) <= 0,0, INT([REPLY_DATE]) - INT([RECEIVED_DATE]))
Create a measure for cases with multiple assignees in text box.
I have the following 2 meausres in my Power BI report: Number of Cases = DISTINCTCOUNTNOBLANK('PBI_XZ_Case_Time_Session'[Case_ID]) Number of Assignees = DISTINCTCOUNTNOBLANK('PBI_XZ_Case_Time_Session'[Created_By]) In the screenshot below, the table has the list of Cases with multiple assignees: the total cases with multiple assignees are 297,371. How may I create a new measure so that the total cases with multiple assignees (297,371) can be used in a text box?The 2nd line of split title doesn't show up
I have the following measure as the dynamic title for my table visual. It has UNICHAR(10) to split long title to 2 lines. Somehow, the 2nd line doesn't show up as shown in screenshot below. I can see the contents of 2nd line when hovering over title. The text wrap of the title is turned off. What's wrong with it? Title of Unassigned Sponsor Payments = VAR __SponsorID = IF( ISFILTERED('PBI_XZ_Fraud_Alerts'[Sponsor ID]), "Sponsor ID is " & SELECTEDVALUE('PBI_XZ_Fraud_Alerts'[Sponsor ID]) ) RETURN "Unassigned, Undetermined, Follow-Up Payments with Risk Assist and Blank Action " & UNICHAR(10) & __SponsorID & " in Last 91 Days up to Yesterday"
Ignore Page Filter in Measure
I have created the following cards at the top of my report: I have added a Page level filter on NET SALES: Level 1 is a hierarchy and Sales exists within Net Sales hence why the card visual has not gone BLANK. But my other cards from Sales + Open Orders to Contribution Margin have gone blank. I have tried using REMOVEFILTERS as below: Sales + Open Orders = CALCULATE( [Amount], 'Profitability Account'[Description] = "Sales + Open Orders", REMOVEFILTERS('Profitability Account'[Description]) ) I even added Level 1 in remove filters but that doesn't work either. How can I fix this? Thanks, S
