Running Total per week/month
Hello, I would like to have a measure in power pivot for the running total that behaves like the one in the normal pivot tables. For instance, in the pivot table below the value are shown as running totals based on the week number (y axis). The running total is based on the week number, and for example after w6 in January it`s still reported the maximum value for the month. Here it's the most i managed to accomplish via power pivot: Here the code for the measure: ActualsRunningTotal:=TOTALYTD(SUM(Actuals[Volume]), Actuals[Date], ALL(Actuals)) Here the structure of the model, highlighted the fields i use in the pivot. I didn`t quite manage to find online how to handle this case, any ideas? Should i adjust the relationship between tables or use different functions? I`m doing it via power pivot because then i would need to combine the measure with other two, for which I only need the normal totals. Thank you in advance, any help is very much appreciated KR
Issue with Multiple date keys and displaying dates on report.
Hello, i am working on a SSAS Tabular model to support Power BI report development. Our main fact table has multiple dates (Order Date, Paid Date, Shipped Date) with the Order Date being the active relationship to the date dimension. One of our reports requires a table that has the following columns (amoung others) Date and Amount by Paid Date. I understand how to build the measure "Amount by Paid Date" using USERELATIONSHIP to active the relationship between my fact.paid_date_key and the dim.date_key. Other than fully duplicating my date dimension to have a "Paid Date Dimension" and use that inactive relationship. how can i include "Paid Date" in my report table?Help Creating DAX Measurements and Tables for Specific Task
Hello Power BI users. I am trying to create a measurement that gives me the top 10 providers experiencing patient's leaving against staff advice (ASA). The date that I am trying to create this measure lives in two separate tables from two different sources. I've connected these two tables using a date table to connect the two tables. In the ASA_Ali_1 table there are multiple programs with dates listed within each row. These dates include program capacity which will need to be used in the calculation. (of note, the ASA_Ali_1 table does not contain case by case basis information but a summation of ASAs that have occurred for each date corresponding to a specific program). In the CLEAN_ASA table, contains case by case i.e., client leaving ASA and the provider that was working with said client at the time of their departure. I need to use the program capacity to generate a relative number of ASAs of a specific provider (contained in CLEAN_ASA) against the average program capacity (contained in ASA_Ali_1). I would like the output to show me the top 10 providers experiencing ASAs for the most recent month. Here is an image of the two data sources being used so you can get an idea of the structure. To summarize, my columns, in report view (with the table visualization), should show the provider, the number of ASAs they have experienced in the most recent month, and the percentage of ASAs relative to that program-capacity that provider was working in for the most recent month. (please note that the providers program they are assigned to work [within the CLEAN_ASA table] is titled, "Housing location," and in Ali_ASA_1 this is identified as "Program"). Some of the program names are identified someone differently therefore a mapping table may have to be made.
How can I calculate this measures?
Good day Kindly advise how can I approach this, I have Affordability table, VoluntaryTermination Table, LearnerTableFETerminations table and Non Collections Table. The problem I'm experiencing relates to the 'Affordability' table's income band. The 'Financial Exclusion' and 'Voluntary Terminations' sections point to their own tables, which each use unique dates, whereas the 'Affordability' table uses a different date. Consequently, when I use a calculated date table to link the three tables—for instance, the 'FE' and 'VT' measures do not connect with the 'Affordability' table to display results based on income band. Furthermore, when I attempt to create a bridge table that consolidates all data into a single table, the challenge arises of selecting which date to use for connection with the date table, given that each table has its distinct dates. Which other options I can use to be able to connect all these tables and be able to link income band with the measures I calculated, e.g. refer to the two dax measures below: = Table.AddColumn(#"Replaced Value", "Income Band", each if [Total_Income] > 110000 then "R110 000 and above" else if [Total_Income] > 90000 then "R90 000 - R110 000" else if [Total_Income] > 70000 then "R70 000 - R90 000" else if [Total_Income] > 50000 then "R50 000 - R70 000" else if [Total_Income] > 20000 then "R20 000 - R50 000" else "R0 - R20 000") Voluntary Terminations = CALCULATE( DISTINCTCOUNT(All_Data[VoluntaryTerminations.ID Learner FK]), All_Data[VoluntaryTerminations.Date Termination Completed]<>BLANK() ) Financial Exclusion = CALCULATE( DISTINCTCOUNT(All_Data[LearnerTableFETerminations.ID]),All_Data[LearnerTableFETerminations.Date FE Completed]<>BLANK() ) The data model:
