Urgent help needed!

[Billy0503]

Hello everyone,
I´m dealing about some weeks with a difficult task and its driving me crazy 😞


The case is that I have 12 Months for a year with amount in $ and each month I has different versions of data. So that means for January I have the Version 01-2018, but it has data for the whole year. In February I have the version 02-2018 with new and other data in there, and so on. So for my report I need to make a comparison where I have to compare two versions (see at the picture below).

In the month which we actually are it´s the current cycle (CC in the column), and the previous month is called last cycle (LC in the column). The problem is that the last cycle always has to be without the month which the currenc cycle is (see the formula 2). Till here it worked fine for me, but now I am searching for an idea or a solution, which has a measure with a automatically formula.

My idea would be, to have to measures.
1. Current cycle CC: which takes always the latest version. Something like date max or max value ( i dont know)
2. Last cycle LC: takes automatically the version before the current cycle AND without the month, in which the latest version is.


Example:
Current cycle is from the version 02-2018 which has February as a month with data. Last cycle would be 01-2018, without the February from the version 02-2018.

I hope I explained it as much as good enough for you to understand my problem 😞 And I hope really that someone could help with this because it is very important to me. If you need some aditional information, please let me know.

[v-ljerr-msft · Accepted solution]

Hi @Billy0503,

If I understand you correctly, you should be able to follow steps below to get the expected result. 

1. Use the formula below to add a new calculate column in FORECAST_INFORMATION table.

FC_DATE_VALUE =
VALUE ( RIGHT ( FORECAST_INFORMATION[FC_DATE], 4 ) ) * 12
    + VALUE ( LEFT ( FORECAST_INFORMATION[FC_DATE], 2 ) )

2. Use the formula below to create a new measure.

Current Cycle Date =
CALCULATE (
    MAX ( FORECAST_INFORMATION[FC_DATE] ),
    ALL ( FORECAST_INFORMATION )
)

3. Then change your exiting measure like below.

W.CC =
SUMX (
    FILTER (
        FC_SALES_DATA,
        RELATED ( FORECAST_INFORMATION[FC_DATE_VALUE] ) = [Current Cycle Date]
    ),
    FC_SALES_DATA[W. TOTAL TRUE CY]
)

W.LC =
SUMX (
    FILTER (
        FC_SALES_DATA,
        RELATED ( FORECAST_INFORMATION[FC_DATE_VALUE] )
            = [Current Cycle Date] - 1
    ),
    FC_SALES_DATA[W. TOTAL TRUE CY]
)
    - FC_SALES_DATA[WEITHT_NOV_TURE_CY]

Regards