Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I've been running into a problem for a while now. I am currently working on a financial balance sheet. Currently my dataset consists of the following columns:
- Description
- Year_Open
- Period credit (These are the changes to the opening value)
- Period debit (These are the changes to the opening value)
- Period (1 to 12 months)
As can be seen in the photo above, the opening value is not a period, I wanted to give it the period 0. This made me want to create the formula:
Period 0 + selected period in slicer.
Does anyone know how I can create this formula in DAX? I also have to ensure that the period 0 is not visible in the slicer, how can I do that?
However, I am not very good with DAX formulas and so far I have solved it as shown below. Only then will the total not change if I adjust the slicer, even though this is a requirement.
Credit =
VAR PeriodCredit = SUM( 'Balance'[Year_Open]) + SUM('Balance'[Period_Debit]) - SUM('Balance'[Period_Credit])
VAR Scoop = NOT(ISINSCOPE(KPI_EOL_xml_GLAccountClassifications[GLClassification2_description]))
VAR ScoopPeriodCredit = SUM(Balance[YearClosedCredit])
VAR Result = IF(Scoop,ScoopPeriodCredit,IF(PeriodCredit < 0, - PeriodCredit))
Return Result
Hopefully someone can help me with this! Thanks!
You can use COALESCE as syntax sugar for the check.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |