Working on a dashboard and need help with two calculations, tried different solutions but all to no avail.
Using the sample of a big dataset indicated below. the goal is to create two dax formulas with similar functions. below are the parameters
- Status ID and Program are related
- Census term changes and indicates the quarter the actual data was captured
- First term indicates the first quarter that the Status ID was recorded into the database.
- Program changes for some Status ID.
- For some census term, the status ID might not show up at all.
Calculation1: Dax formula that cycles through big dataset and looks for each status ID/program relationship and calculate the sum of all the status ID as the census term changes.
Calculation 2: Dax formula that cycles through the dataset and looks for the each status ID and calculate the sum of all the status iD as the census term changes.
Any help will be greatly appreciated.
Thanks.
| Status ID | Program | First Term | Census Term |
| 1694 | Science | 08/FA | 09/FA |
| 2095 | Science | 07/FA | 09/FA |
| 2444 | Science | 06/FA | 09/FA |
| 2758 | Arts | 08/SP | 09/FA |
| 3597 | Arts | 94/FA | 09/FA |
| 4761 | Politics | 05/WI | 09/FA |
| 6329 | Politics | 94/SP | 09/FA |
| 7191 | Politics | 96/FA | 09/FA |
| 4578 | Science | 05/WI | 09/FA |
| 4789 | Science | 94/SP | 09/FA |
| 5000 | Science | 96/FA | 09/FA |
| 5211 | Science | 05/WI | 09/FA |
| 5422 | Arts | 94/SP | 09/FA |
| 5633 | Arts | 96/FA | 09/FA |
| 5844 | Politics | 05/WI | 09/FA |
| 6055 | Politics | 94/SP | 09/FA |
| 6266 | Politics | 96/FA | 09/FA |
| 1694 | Politics | 08/FA | 10/FA |
| 2095 | Politics | 07/FA | 10/FA |
| 2444 | Politics | 06/FA | 10/FA |
| 2758 | Politics | 08/SP | 10/FA |
| 3597 | Politics | 94/FA | 10/FA |
| 4761 | Politics | 05/WI | 10/FA |
| 6329 | Politics | 94/SP | 10/FA |
| 7191 | Politics | 96/FA | 10/FA |
| 4578 | Science | 05/WI | 10/FA |
| 4789 | Science | 94/SP | 10/FA |
| 5000 | Science | 96/FA | 10/FA |
| 5211 | Science | 05/WI | 10/FA |
| 5422 | Arts | 94/SP | 10/FA |
| 5633 | Arts | 96/FA | 10/FA |
| 5844 | Politics | 05/WI | 10/FA |
| 6055 | Politics | 94/SP | 10/FA |
| 6266 | Politics | 96/FA | 10/FA |
