Forum Discussion

Oomsen's avatar
Oomsen
Helper III
7 years ago

Exclude next year

I'm using the function below and would like to exclude values for the coming year. = IF(MIN(Dimdate[Maand van jaar])<=MONTH(TODAY());[Cashflow **bleep**]-[Cashflow];""   The function is calculatin...
Need Help
Oomsen's avatar
Oomsen
Helper III
7 years ago

This is the 2018 data. I would like to create the samen overview for 2019. All measures are working except the opening balance (beginsaldo).

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi Oomsen,

 

Should it be 0 for the year 2019? Please try the formula below.

 

a =
IF (
    MIN ( Dimdate[Maand van jaar] ) <= MONTH ( TODAY () )
        && MIN ( Dimdate[Year] ) <= YEAR ( TODAY () );
    [Cashflow **bleep**] - [Cashflow];
    ""
)

 

Best Regards,
Dale

  • Oomsen's avatar
    Oomsen
    Helper III
    7 years ago

    It should be "0" in 2019 indeed. I have tried this function but power bi say: "the visual element can not be opened".

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Oomsen,

       

      That seems not related to the formula. Can you get something in another visual like Table? 

      Can you share a sample file only with limit data that can reproduce the issue?

       

       

      Best Regards,
      Dale

      • Oomsen's avatar
        Oomsen
        Helper III
        7 years ago

        I wasn't able to add the document so below the date:

         

        dateopening balancecashflowreceivablesales orderspayablecostsclosing balance
        1-1-2018         362.441,00   -94.613,00           267.828,00
        1-2-2018         267.828,00 -271.768,00                2,00           -3.942,00
        1-3-2018            -3.942,00 -298.819,00         -302.761,00
        1-4-2018        -302.761,00   -75.327,00         -378.088,00
        1-5-2018        -378.088,00     27.366,00         -350.722,00
        1-6-2018        -350.722,00   -14.062,00        -170,00          160,00      -365.114,00
        1-7-2018        -365.114,00     31.476,00   101.559,00      -435.197,00
        1-8-2018        -435.197,00   630.486,00           195.289,00
        1-9-2018         195.289,00 -164.167,00         481,00          199,00          31.404,00
        1-10-2018            31.404,00     49.742,00   85.097,00    10.984,00        155.259,00
        1-11-2018         155.259,00   -65.174,00 143.904,00 -50.679,00 167.045,00          16.265,00
        1-12-2018            16.265,00       7.741,00   36.732,00                  -   106.045,00        -45.307,00
        1-1-2019          -45.307,00     63.000,00   30.079,00   95.490,00     -107.876,00
        1-2-2019        -107.876,00     82.500,00    92.253,00     -117.629,00
        1-3-2019        -117.629,00       91.312,00     -208.941,00
        1-4-2019        -208.941,00       93.996,00     -302.937,00
        1-5-2019        -302.937,00       99.100,00     -402.037,00
        1-6-2019        -402.037,00       97.426,00     -499.463,00
        1-7-2019        -499.463,00       95.468,00     -594.931,00
        1-8-2019        -594.931,00       95.464,00     -690.395,00
        1-9-2019        -690.395,00       96.008,00     -786.403,00
        1-10-2019        -786.403,00       96.805,00     -883.208,00
        1-11-2019        -883.208,00       97.282,00     -980.490,00
        1-12-2019        -980.490,00       96.975,00  -1.077.465,00