Calculate Sum and Filter with IF conditions
Hi, I have a monthly trend table where I need to calculate the sum of patients with few filters and conditions applied. See the below measure - Patients_Adj = var test = CALCULATE(SUM(Patients[Patients]),Patients[TYPE] = "Paid", Patients[PRODUCT_FK] = "Product A") return IF(MAX('Date'[Month])>=[max_Date],test,0) This measure is giving correct output at row level, but totals are incorrect because this is doing the aggregation first and then applying the return condition. Is there a way I can re-write this measure so that conditions are applied first and then the sum aggregation happens. This is important because I need to calculate the cumulative number on top of this measure so that totals has to be accurate. Below is the table that I am using - Thanks!
TotalYTD or DatesYTD based on SLicer filter
Hello everyone, I have a totalytd measure, and creating a bar chart based on date and total sales. Also I have a slicer which contains Month Name. If I filter for March month, the bar chart will show data only for March month but my requirement should be like the visual should display till March month (Jan, Feb and Mar). Likewise if I select June month in the slicer, the bar chart should display data from January to June. How to achieve this, please advice.Calendar function dependent on 2 date columns
Hi, I have a Date table in my data model where I need to use the Calendar function for the date column based on 2 date columns (ENR_DATE and ADM_DATE) from the fact table. I am currently using - CALENDAR (MINX ('02 Fact - MA', '02 Fact - MA'[ENR_DATE]), MAXX ('02 Fact - MA', '02 Fact - MA'[ENR_DATE])) but my range should be dependent on 2 columns. Both columns are present in the same fact table. I want to take MIN from ENR_DATE and ADM_DATE and similarly max from ENR_DATE and ADM_DATE. Is that possible to do? Thanks!
Dax calculations to display two dates from one date column, and the time difference between the two.
Hello all, I need your help to improve on some Dax functions 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 to get the results I have shown in the Power report below 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]))
